如何在SQL Server中同时向两个表插入数据？

Question

假设我的表结构看起来像这样:

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)
)

第一个表的[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;

对于只插入少量记录的情况，以上方法都很好用。但是我的需求是一次性插入几十万行甚至一百万行数据。由于数据来自另一个表格，如果只往单个表格中插入数据，方法也很简单，只需要执行以下操作：

INSERT INTO [table] ([data])
SELECT [data] FROM [external_table];

但是我应该如何做到这一点并将数据分割成[table1]和[table2]，同时在执行此操作时仍然使用适当的[table1_id]更新[table2]？这是否可能？

- soapergem

---

很好的问题，许多SQL初学者常常提出。 - Mark Schultheiss

Answer 1

试试这个：

insert into [table] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

更新: Re:

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] 

在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]
;

- Denis Valeev

---

Denis，我只用OUTPUT将数据写入表变量。你能否使用它直接插入到实时表中？ - Bill

顺便提一下，没有必要在begin tran...commit tran语句中进行封装，因为它明显会在一个事务中运行。 - Denis Valeev

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] - soapergem

我最初对此感到兴奋，但后来遇到了这个错误：“OUTPUT INTO 子句的目标表不能有任何启用的检查约束”——不可能发生！哦，好吧，回到使用临时分段表或表变量来存储输出。无论如何，还是很好知道 :) - onedaywhen

@onedaywhen 哈哈哈！:))) 是的，有很多不需要出现在输出表中的东西（fk、约束、触发器），所以基本上除了暂存表之外是没用的。 - Denis Valeev

Answer 2

我也曾经遇到过这个问题，发现最好的方法是使用CURSOR。

我尝试过Denis的解决方案，但正如他所提到的，在一个INSERT语句中无法输出外部列，而且当通过选择插入多行时，MERGE无法工作。

因此，我使用了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

- IFink

Answer 3

请留意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

- Brian

Answer 4

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;

- Bill

---

鉴于丹尼斯对我的评论的回应，他的解决方案比我的更加简洁。 - Bill

在 output 子句中，您不能使用 source.id、new.id。对于 insert，只允许在其中使用 inserted.*。对于 delete、update 和 merge，可以从指定的表中包含一列。 - Denis Valeev

Answer 5

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)
)

-----主查询------

insert into #temp1(name,profession)

output inserted.name,inserted.profession into #temp2

select 'Shekhar','IT'

- Shekhar Kumar

---

这与被接受的答案中的想法相同（使用输出子句执行第二个插入）。 - barbsan

Answer 6

你可以编写一个存储过程来迭代你提出的事务。迭代器将是包含源数据的表的游标。

- mlschechter

---

有基于集合的解决方案时，永远不要使用迭代。 - HLGEM

@HLGEM - 鉴于我的错误，您推荐哪个解决方案？ - mlschechter

一定要使用输出子句到临时表。 - HLGEM

Answer 7

另一个选项是分别运行两个插入语句，将FK列保留为空，然后运行更新语句以正确填充它。

如果两个表中没有自然匹配的内容（很可能如此），则创建一个临时GUID列，并在数据中填充此列并插入到两个字段中。然后，您可以使用正确的FK进行更新并将GUID设置为null。

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

- cjk

---

如何为两个表设置相同的“JoinGuid”？如果您在“Table1”中有一条记录并且在“Table2”中有一条记录，则这很简单，但是如果有多行记录，我无法想象如何实现。 - Illia Ratkevych

在你的代码中的某个时刻，你应该能够关联数据，在那个时候添加 guid。 - cjk