大插入两个表中.第一个表将使用其生成的ID馈送给第二个表 [英] large insert in two tables. First table will feed second table with its generated Id
问题描述
关于如何对以下查询进行t-sql编程的一个问题:
One question about how to t-sql program the following query:
表1 我在两列的表格中插入400.000手机号码.要插入的数字和身份ID.
Table 1 I insert 400.000 mobilephonenumbers in a table with two columns. The number to insert and identity id.
表2 第二个表称为SendList.它是一个包含3列,身份ID,列表ID和电话号码ID的列表.
Table 2 The second table is called SendList. It is a list with 3columns, a identity id, a List id, and a phonenumberid.
表3 被称为ListInfo并包含PK列表ID.以及有关列表的信息.
Table 3 Is called ListInfo and contains PK list id. and info about the list.
我的问题是我应该如何使用T-sql:
My question is how should I using T-sql:
将具有电话号码的大列表插入表1,插入从电话号码插入处生成的ID.从表1到表2.不能花很长时间,那是我的问题.
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.
非常感谢有人指导我.
谢谢
塞巴斯蒂安
推荐答案
您正在使用哪个版本的SQL Server?如果使用的是2008,则可以使用OUTPUT子句插入多个记录,并将所有标识记录输出到表变量.然后,您可以使用它来插入子表.
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.
这篇关于大插入两个表中.第一个表将使用其生成的ID馈送给第二个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!