Insert Into () Select or any other good way for

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

SQL Server 2008 Forums

Transact-SQL (2008)

Insert Into () Select or any other good way for

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-03-17 : 13:46:19

I needed the geeks Suggestion about using Insert Into () Select or any other good way for adding up-to millions of records

I am creating replica records of the selected customers.From the grid
Each customers replica will have an entry in the main table then records in the 15 child tables with same records of the selected cutomers.[not necessary that all child have records]
Some of 2 to 3 of my tables may have upto 2 million records.

I am using enterprise library data access block and sql server 2008
Now my concern is how should i go about inserting
My current thinking is I will call 15 stored procedure which will have query like

Sp1

Insert into CustomerChildTable1 (F1,F2,F3, addedby)
select F1 , F2, F3, @addedby from CustomerChildTable1 where customerid = @customerid and isdeleted = 0

Sp2

Insert into CustomerChildTable2 (A1,A2,A3, F4, addedby)
select A1 , A2, A3,F4 @addedby from CustomerChildTable2 where customerid = @customerid and isdeleted = 0

....

Please suggest me any good way [For me the most critical thing is the quickest response]

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 14:01:03

Quickest response will, I think, be by bulk load from a file pre-sorted in Clustered Index order, and providing the appropriate hint (that the import file is ordered by clustered index).

You could export those files from your database with the SELECT statement you have indicated above ... (with an ORDER BY on Clustered Index keys of the Target Table of course

)

Drop all non-clustered indexes before import, and recreate them afterwards. You may want to drop FKeys (if you are confident about the referential integrity of the import data) and recreate them afterwards too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 14:01:32

do you want each of tables id to be retrieved and inserted as pk value in its child?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2010-03-17 : 14:17:29

No visakh.I have composite primary key
My field might be look like
Insert into CustomerChildTable1 (CK1, CK2, Ck3, F1,F2,F3, addedby)
select @CK1, @CK2,CK3, F1 , F2, F3, @addedby from CustomerChildTable1 where customerid = @customerid and isdeleted = 0

I can't load it from a file as i have to copy the data from the same table.

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Kristen
Test

22859 Posts

Posted - 2010-03-17 : 14:40:53

"I can't load it from a file as i have to copy the data from the same table."

Yup, I realised that, but you can export from Table to File and import from File to Table.

I expect that, for several million rows, INSERT INTO TableA SELECT ... FROM TableA will be the slowest approach. Huge TLogs, significant Constraint checking, Lots of index activity and fragmentation, lock escalation and so on ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources