假设我有一个主表-- Order的名称和名为OrderDetail的详细表,OrderId是标识密钥,并在OrderDetail中用作外键。现在,我希望将带有存储过程的批量数据插入到Order中,然后将相关详细信息插入OrderDetail表。有人能告诉我最好的方法吗?如何从主表和我使用的明细表中获取标识值?
发布于 2017-04-24 08:11:39
您可以使用OUTPUT子句如下:
将BULK INSERT存储到Orders表中,并将BULK INSERT中的所有Id's存储到表变量中。然后,将详细信息插入到OrderDetail中,从已经存储它们的表变量中获取OrderId。
您可以检查此代码的工作演示这里。
DECLARE @outputtbl TABLE ( id INT );
--here you will store the bulk inserted id's
--here you will do the bulk insert (note that i used a union all of several
selects as a "source" for your bulk insert)
INSERT INTO Orders
OUTPUT inserted.id
INTO @outputtbl ( id )
SELECT *
FROM ( SELECT 1 AS id ,
GETDATE() AS dt
UNION ALL
SELECT 2 AS id ,
GETDATE() AS dt
UNION ALL
SELECT 3 AS id ,
GETDATE() AS dt
UNION ALL
SELECT 4 AS id ,
GETDATE() AS dt
) t;
--inserting into OrderDetail, OrderId's from the table variable and other fields as per your logic. (`item name` here it's just an example)
INSERT INTO OrderDetail
( orderid, itemname )
SELECT id ,
'itemx'
FROM @outputtbl;我创建了两个简单的表Orders和OrderDetail来模拟这个问题。
发布于 2017-04-24 08:43:24
这只是一个示例表,我将大量的示例数据插入到我的表中。
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter < 50000
BEGIN
INSERT [SampleTableName] VALUES(Id)
SELECT
NEWID() -- i have a column sample_id so i am entrying random
newid() into that
ABS (CHECKSUM(NEWID())) % 60 + 1,
DATEADD ( DAY, ABS(CHECKSUM(NEWID()) % 3650), '2007-04-01')
-- i have a sample date field as well and i am entrying 10
years of date in that
SET @Counter += 1
ENDhttps://stackoverflow.com/questions/43582343
复制相似问题