large insert in two tables. First table will feed second table with its generated Id

Question

Hi all,

One question about how to t-sql program the following query:

Table 1 I insert 400.000 mobilephonenumbers in a table with two columns. The number to insert and identity id.

Table 2 The second table is called SendList. It is a list with 3columns, a identity id, a List id, and a phonenumberid.

Table 3 Is called ListInfo and contains PK list id. and info about the list.

My question is how should I using T-sql:

Insert large list with phonenumbers to table 1, insert the generated id from the insert of phonenum. in table1, to table 2. AND in a optimized way. It cant take long time, that is my problem.

Greatly appreciated if someone could guide me on this one.

Thanks

Sebastian

Answer 1

What version of SQL Server are you using? If you are using 2008 you can use the OUTPUT clause to insert multiple records and output all the identity records to a table variable. Then you can use this to insert to the child tables.

DECLARE @MyTableVar table(MyID int); INSERT MyTabLe (field1, field2)
OUTPUT INSERTED.MyID INTO @MyTableVar select Field1, Field2 from MyOtherTable where field3 = 'test'

--Display the result set of the table variable. Insert MyChildTable (myID,field1, field2) Select MyID, test, getdate() from @MyTableVar

I've not tried this directly with a bulk insert, but you could always bulkinsert to a staging table and then use the processs, described above. Inserting groups of records is much much faster than one at a time.