insert 4,000,000 rows in minimum second

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

insert 4,000,000 rows in minimum second

Author

Topic

Gaurang.patel7
Starting Member

2 Posts

Posted - 2011-05-19 : 05:50:55

Hi All,
we have requirement Taht we need to insert 4,000,000 in destination table,Destination Table is heap ,no index.
Destination table has following Structure and Source table has also same

CREATE TABLE [dbo].[Desination]
(
[Col1] [nchar] (24) NOT NULL,
[Col2] [int] NOT NULL,
[Col3] [int] NOT NULL,
[Col4] [tinyint] NOT NULL,
[col5] [tinyint] NULL
) ON [PRIMARY]
GO

While i write statement
like
INSERT INTO dbo.Desination
( Col1, Col2, Col3, Col4, col5 )
SELECT Col1 ,
Col2 ,
Col3 ,
Col4 ,
col5 FROM dbo.SourceTable with(nolock)

it consumes 15 seconds average.

Both table are same Database.

can i make it more faster within 5 seconds,i desperately needs it.can i make use of Bulk insert?

Please not from source table col2,col3,col4,col5 will be same value for Destination table. while col1 will have Sessionid for that user,so all 4,000,000 row will have same value in col1.mdf file is on SSD drive while ndf file on SAS.

Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-19 : 06:04:32

bcp out and in
drop destination table and select into.
Partition destination table, select into a new table then swap it into the destination
SSIS

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gaurang.patel7
Starting Member

2 Posts

Posted - 2011-05-19 : 06:15:49

can you tell me how to write BCP in and out with same server as both table lies in same database.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-19 : 06:28:48

something like (for integrated security)
exec master..xp_cmdshell 'bcp mydb..mysourcetbl out c:\myfile.txt -Smyserver -T -N'
exec master..xp_cmdshell 'bcp mydb..mydesttbl in c:\myfile.txt -Smyserver -T -N'

Uses native data types which are much faster than ascii

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources