如何在 SQL Server 中同时将数据插入到两个表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3712678/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I INSERT data into two tables simultaneously in SQL Server?
提问by soapergem
Let's say my table structure looks something like this:
假设我的表结构如下所示：
CREATE TABLE [dbo].[table1] (
[id] [int] IDENTITY(1,1) NOT NULL,
[data] [varchar](255) NOT NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
)
CREATE TABLE [dbo].[table2] (
[id] [int] IDENTITY(1,1) NOT NULL,
[table1_id] [int] NOT NULL,
[data] [varchar](255) NOT NULL,
CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
)
The [id]field of the first table corresponds to the [table1_id]field of the second. What I would like to do is insert data into both tables in a single transaction. Now I already know how to do this by doing INSERT-SELECT-INSERT, like this:
第[id]一个表的[table1_id]字段对应于第二个表的字段。我想做的是在单个事务中将数据插入到两个表中。现在我已经知道如何通过执行 INSERT-SELECT-INSERT 来做到这一点，如下所示：
BEGIN TRANSACTION;
DECLARE @id [int];
INSERT INTO [table1] ([data]) VALUES ('row 1');
SELECT @id = SCOPE_IDENTITY();
INSERT INTO [table2] ([table1_id], [data]) VALUES (@id, 'more of row 1');
COMMIT TRANSACTION;
That's all good and fine for small cases like that where you're only inserting maybe a handful of rows. But what I need to do is insert a couple hundred thousand rows, or possibly even a million rows, all at once. The data is coming from another table, so if I was only inserting it into a single table, it would be easy, I'd just have to do this:
对于像这样您只插入少数行的小案例来说，这一切都很好。但我需要做的是一次插入几十万行，甚至可能是一百万行。数据来自另一个表，所以如果我只是将它插入到一个表中，那就很容易了，我只需要这样做：
INSERT INTO [table] ([data])
SELECT [data] FROM [external_table];
But how would I do this and split the data into [table1]and [table2], and still update [table2]with the appropriate [table1_id]as I'm doing it? Is that even possible?
但是我将如何做到这一点并将数据拆分为[table1]and [table2]，并且[table2]在[table1_id]我这样做时仍然使用适当的更新？这甚至可能吗？
采纳答案by Denis Valeev
Try this:
尝试这个：
insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]
UPDATE:Re:
更新：回复：
Denis - this seems very close to what I want to do, but perhaps you could fix the following SQL statement for me? Basically the [data] in [table1] and the [data] in [table2] represent two different/distinct columns from [external_table]. The statement you posted above only works when you want the [data] columns to be the same.
Denis - 这似乎与我想要做的非常接近，但也许您可以为我修复以下 SQL 语句？基本上，[table1] 中的 [data] 和 [table2] 中的 [data] 代表来自 [external_table] 的两个不同/不同的列。您上面发布的语句仅在您希望 [data] 列相同时才有效。
INSERT INTO [table1] ([data])
OUTPUT [inserted].[id], [external_table].[col2]
INTO [table2] SELECT [col1]
FROM [external_table]
It's impossible to output external columns in an insertstatement, so I think you could do something like this
在insert语句中输出外部列是不可能的，所以我认为你可以做这样的事情
merge into [table1] as t
using [external_table] as s
on 1=0 --modify this predicate as necessary
when not matched then insert (data)
values (s.[col1])
output inserted.id, s.[col2] into [table2]
;
回答by IFink
I was also struggling with this problem, and find that the best way is to use a CURSOR.
我也在努力解决这个问题，发现最好的方法是使用CURSOR。
I have tried Denis solution with OUTPUT, but as he mentiond, it's impossible to output external columns in an insert statement, and the MERGE can't work when insert multiple rows by select.
我已经尝试过使用 OUTPUT 的 Denis 解决方案，但正如他所提到的，不可能在插入语句中输出外部列，并且在通过选择插入多行时 MERGE 无法工作。
So, i've used a CURSOR, for each row in the outer table, i've done a INSERT, then use the @@IDENTITY for another INSERT.
所以，我使用了一个 CURSOR，对于外部表中的每一行，我都做了一个 INSERT，然后使用 @@IDENTITY 进行另一个 INSERT。
DECLARE @OuterID int
DECLARE MY_CURSOR CURSOR
LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT ID FROM [external_Table]
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @OuterID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO [Table] (data)
SELECT data
FROM [external_Table] where ID = @OuterID
INSERT INTO [second_table] (FK,OuterID)
VALUES(@OuterID,@@identity)
FETCH NEXT FROM MY_CURSOR INTO @OuterID
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
回答by Brian
Keep a look out for SQL Server to support the 'INSERT ALL' Statement. Oracle has it already, it looks like this (SQL Cookbook):
请留意 SQL Server 是否支持“INSERT ALL”语句。Oracle 已经有了，它看起来像这样 ( SQL Cookbook)：
insert all
when loc in ('NEW YORK', 'BOSTON') THEN
into dept_east(deptno, dname, loc) values(deptno, dname, loc)
when loc in ('CHICAGO') THEN
into dept_mid(deptno, dname, loc) values(deptno, dname, loc)
else
into dept_west(deptno, dname, loc) values(deptno, dname, loc)
select deptno, dname, loc
from dept
回答by Shekhar Kumar
Create table #temp1
(
id int identity(1,1),
name varchar(50),
profession varchar(50)
)
Create table #temp2
(
id int identity(1,1),
name varchar(50),
profession varchar(50)
)
-----main query ------
-----主要查询------
insert into #temp1(name,profession)
output inserted.name,inserted.profession into #temp2
select 'Shekhar','IT'
回答by Bill
BEGIN TRANSACTION;
DECLARE @tblMapping table(sourceid int, destid int)
INSERT INTO [table1] ([data])
OUTPUT source.id, new.id
Select [data] from [external_table] source;
INSERT INTO [table2] ([table1_id], [data])
Select map.destid, source.[more data]
from [external_table] source
inner join @tblMapping map on source.id=map.sourceid;
COMMIT TRANSACTION;
回答by cjk
Another option is to run the two inserts separately, leaving the FK column null, then running an update to poulate it correctly.
另一种选择是分别运行两个插入，将 FK 列保留为空，然后运行更新以正确填充它。
If there is nothing natural stored within the two tables that match from one record to another (likely) then create a temporary GUID column and populate this in your data and insert to both fields. Then you can update with the proper FK and null out the GUIDs.
如果在从一个记录匹配到另一个（可能）的两个表中没有自然存储的内容，则创建一个临时 GUID 列并将其填充到您的数据中并插入到两个字段中。然后，您可以使用正确的 FK 进行更新并清除 GUID。
E.g.:
例如：
CREATE TABLE [dbo].[table1] (
[id] [int] IDENTITY(1,1) NOT NULL,
[data] [varchar](255) NOT NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC),
JoinGuid UniqueIdentifier NULL
)
CREATE TABLE [dbo].[table2] (
[id] [int] IDENTITY(1,1) NOT NULL,
[table1_id] [int] NULL,
[data] [varchar](255) NOT NULL,
CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC),
JoinGuid UniqueIdentifier NULL
)
INSERT INTO Table1....
INSERT INTO Table2....
UPDATE b
SET table1_id = a.id
FROM Table1 a
JOIN Table2 b on a.JoinGuid = b.JoinGuid
WHERE b.table1_id IS NULL
UPDATE Table1 SET JoinGuid = NULL
UPDATE Table2 SET JoinGuid = NULL
回答by mlschechter
You could write a stored procedure that iterates over the transaction that you have proposed. The iterator would be the cursor for the table that contains the source data.
您可以编写一个存储过程来迭代您提出的事务。迭代器将是包含源数据的表的游标。
