SQL Server：是否可以同时插入两个表？

Question

我的数据库中包含三个表，分别叫做 Object_Table、Data_Table 和 Link_Table。链接表只包含两列，一个对象记录的标识和一个数据记录的标识。

我想将与一个给定对象标识相关联的来自DATA_TABLE的数据复制，并将相应的记录插入到另一个给定对象标识对应的Data_Table和Link_Table中。

我可以通过选择到一个表变量，然后循环执行每次迭代的两个插入操作来实现这一点。

这是最好的方法吗？

编辑：我想避免使用循环有两个原因，第一个原因是我懒，循环/临时表需要更多的代码，更多的代码意味着出错的地方更多，第二个原因是性能问题。

我可以在一个插入操作中复制所有数据，但如何让链接表与新数据记录相关联，其中每条记录都具有新的ID？

- tpower

---

我不明白为什么要尝试使用一个插入语句，而使用两个插入语句可以完美地解决问题。你是想确保这两个插入语句都已经完成了吗？那么你需要检查一下提交/回滚指令。 - Philippe Grondier

我只需要两个插入，但需要插入链接表的身份是在第一个插入中生成的身份。 - tpower

Answer 1

在一个语句中：不行。 在一个事务中：可以。

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

好消息是上面的代码也保证了原子性，并且可以像单个语句一样在客户端应用程序中以一个SQL字符串的形式发送到服务器，并通过一个函数调用来执行。您还可以对一个表应用触发器，以实现单个插入的效果。但是，这最终仍然是两个语句，您可能不希望对每个插入运行触发器。

- Joel Coehoorn

---

@Joel，好问题。可能有人希望有一个替代现实，而你成了传递坏消息的人。 ;) - Kirk Woll

最佳答案！我浪费了很多时间尝试弄清如何在单个查询中链接插入。谢谢！！ - MonkeyDoug

这并没有解决问题。他想要插入从Object_Table读取的数据，也就是一个insert into ... select ...语句。那么以上的代码如何读取或循环遍历Object_Table的数据呢？你仍然需要使用一个表变量，而提问者并不想这样做。 - hofnarwillie

@hofnarwillie 是正确的，使用这种解决方案仍然需要显式地声明循环。 - Fortunato

当然，这解决了问题。也许我没有编写完整的代码，但是OP也没有分享他想要复制的所有列。此答案展示的功能将允许OP完成所需操作...运行查询以创建记录，获取新记录的ID，并以原子方式使用该ID进行第二个记录。OP已经知道如何执行插入/选择操作。这是他缺少的部分。 - Joel Coehoorn

Answer 2

您仍需要两个 INSERT 语句，但听起来您想从第一个插入获取 IDENTITY 并在第二个插入中使用它，在这种情况下，您可能需要了解 OUTPUT 或 OUTPUT INTO： http://msdn.microsoft.com/en-us/library/ms177564.aspx

- Cade Roux

---

在一个SQL语句中使用两次"OUTPUT INTO"是否可行？ - Guokas

@V.Wu 我不这么认为，我需要设置一个测试来查看。 - Cade Roux

Answer 3

以下是我使用表变量设置的情况。

DECLARE @Object_Table TABLE
(
    Id INT NOT NULL PRIMARY KEY
)

DECLARE @Link_Table TABLE
(
    ObjectId INT NOT NULL,
    DataId INT NOT NULL
)

DECLARE @Data_Table TABLE
(
    Id INT NOT NULL Identity(1,1),
    Data VARCHAR(50) NOT NULL
)

-- create two objects '1' and '2'
INSERT INTO @Object_Table (Id) VALUES (1)
INSERT INTO @Object_Table (Id) VALUES (2)

-- create some data
INSERT INTO @Data_Table (Data) VALUES ('Data One')
INSERT INTO @Data_Table (Data) VALUES ('Data Two')

-- link all data to first object
INSERT INTO @Link_Table (ObjectId, DataId)
SELECT Objects.Id, Data.Id
FROM @Object_Table AS Objects, @Data_Table AS Data
WHERE Objects.Id = 1

感谢另一个答案指向了OUTPUT子句，我可以演示解决方案：

-- now I want to copy the data from from object 1 to object 2 without looping
INSERT INTO @Data_Table (Data)
OUTPUT 2, INSERTED.Id INTO @Link_Table (ObjectId, DataId)
SELECT Data.Data
FROM @Data_Table AS Data INNER JOIN @Link_Table AS Link ON Data.Id = Link.DataId
                INNER JOIN @Object_Table AS Objects ON Link.ObjectId = Objects.Id 
WHERE Objects.Id = 1

然而，在现实生活中，事情并不那么简单，因为出现了以下错误:

在（主键、外键）关系的任何一侧都不能使用 OUTPUT INTO 子句。

我仍然可以将数据 OUTPUT INTO 到一个临时表中，再通过正常的插入语句完成。这样我就可以避免循环，但是无法避免使用临时表。

- tpower

---

如果您正在使用SQL Server 2008，请参阅我的答案，了解如何解决此错误。 - Martin Smith

我认为这不是一个有效的答案，因为它使用数据库表，在高负载数据库系统中会增加显著的开销。 最好的解决方案是"Sergei Zinovyev"提供的补充，这是对上面排名较高的答案的补充。请注意，如果没有"SET XACT_ABORT ON;"子句，排名较高的答案是不正确的。 - Ahmed

变量名非常令人困惑。 - Vasil Valchev

Answer 4

我想强调使用

SET XACT_ABORT ON;

用于包含多个 SQL 语句的 MSSQL 事务。

参见：https://msdn.microsoft.com/en-us/library/ms188792.aspx，其中提供了一个非常好的示例。

因此，最终代码应如下所示：

SET XACT_ABORT ON;

BEGIN TRANSACTION
   DECLARE @DataID int;
   INSERT INTO DataTable (Column1 ...) VALUES (....);
   SELECT @DataID = scope_identity();
   INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT

- Sergei Zinovyev

---

批准的答案不一定是最优的。 得票最高的答案有一个你提到过的缺陷。 - Ahmed

Answer 5

听起来Link表记录了Object表和Data表之间的多对多关系。

我的建议是使用存储过程来管理事务。当你想要向Object表或Data表插入数据时，执行插入操作，获取新的ID并将它们插入到Link表中。

这样可以使你的所有逻辑都封装在一个易于调用的存储过程中。

- TrickyNixon

---

为什么没有其他人给你点赞呢？存储过程是明显且最佳的方式。将你的回答与Joel Coehoorn的回答相结合，你就得到了最好的答案！ - Rhyous

Answer 6

如果您希望操作更加原子化，我建议将其包装在事务中。这样，您就可以确保两个操作都已经完成或者两个操作都没有完成。

- Craig

---

如果将操作包装在事务中，则这些操作是原子性的，而不是“大致”原子性。除非您指定，否则不一定保证隔离级别。 - Dave Markle

Answer 7

你可以创建一个视图，选择插入语句所需的列名，添加一个INSTEAD OF INSERT触发器，并插入到这个视图中。

- devio

Answer 8

在Oracle中进行多表插入之前，您可以使用一个技巧，即在视图上定义一个INSTEAD OF触发器，以执行插入操作。这在SQL Server中可以实现吗？

- David Aldridge

Answer 9

一次只能对一个表执行插入操作。多个插入必须有多个语句。

我不知道你是否需要循环遍历表变量 - 不能只是将数据大规模插入一个表，然后再大规模插入另一个表吗？

顺便说一下 - 我猜你的意思是从Object_Table中复制数据；否则这个问题就没有意义。

- Carlton Jenke

Answer 10

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE InsetIntoTwoTable

(
@name nvarchar(50),
@Email nvarchar(50)
)

AS
BEGIN

    SET NOCOUNT ON;


    insert into dbo.info(name) values (@name)
    insert into dbo.login(Email) values (@Email)
END
GO

- FakirPori

---

你能加一些解释吗？ - Kyll