SQL Server：是否可以同时插入两个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/175066/
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
SQL Server: Is it possible to insert into two tables at the same time?
提问by tpower
My database contains three tables called Object_Table, Data_Tableand Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.
我的数据库包含三个名为Object_Table,Data_Table和 的表Link_Table。链接表只包含两列，对象记录的身份和数据记录的身份。
I want to copy the data from DATA_TABLEwhere it is linked to one given object identity and insert corresponding records into Data_Tableand Link_Tablefor a different given object identity.
我想复制从数据DATA_TABLE在那里它与一个给定对象的身份和插入相应的记录进入Data_Table并Link_Table针对不同的给定对象的身份。
I cando this by selecting into a table variable and the looping through doing two inserts for each iteration.
我可以通过选择一个表变量并为每次迭代执行两次插入循环来做到这一点。
Is this the best way to do it?
这是最好的方法吗？
Edit: I want to avoid a loop for two reason, the first is that I'm lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.
编辑：我想避免循环有两个原因，第一个是我很懒，循环/临时表需要更多的代码，更多的代码意味着更多的地方出错，第二个原因是对性能的关注。
I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?
我可以在一次插入中复制所有数据，但是如何让链接表链接到每条记录都有一个新 ID 的新数据记录？
采纳答案by tpower
The following sets up the situation I had, using table variables.
下面使用表变量设置了我的情况。
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
Thanks to another answerthat pointed me towards the OUTPUT clause I can demonstrate a solution:
感谢另一个将我指向 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
It turns out however that it is not that simple in real life because of the following error
然而事实证明，由于以下错误，在现实生活中并没有那么简单
the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship
OUTPUT INTO 子句不能位于（主键、外键）关系的任一侧
I can still OUTPUT INTOa temp table and then finish with normal insert. So I can avoid my loop but I cannot avoid the temp table.
我仍然可以OUTPUT INTO使用临时表，然后以正常插入完成。所以我可以避免我的循环，但我不能避免临时表。
回答by Joel Coehoorn
In one statement: No.
在一份声明中：不。
In one transaction: Yes
一笔交易：是
BEGIN TRANSACTION
DECLARE @DataID int;
INSERT INTO DataTable (Column1 ...) VALUES (....);
SELECT @DataID = scope_identity();
INSERT INTO LinkTable VALUES (@ObjectID, @DataID);
COMMIT
The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client application with one sql string in a single function call as if it were one statement. You could also apply a trigger to one table to get the effect of a single insert. However, it's ultimately still two statements and you probably don't want to run the trigger for everyinsert.
好消息是，上面的代码也保证是原子的，并且可以从客户端应用程序在单个函数调用中使用一个 sql 字符串发送到服务器，就好像它是一个语句一样。您还可以将触发器应用于一个表以获得单个插入的效果。但是，它最终仍然是两个语句，您可能不想为每个插入运行触发器。
回答by Cade Roux
You still need two INSERTstatements, but it sounds like you want to get the IDENTITYfrom the first insert and use it in the second, in which case, you might want to look into OUTPUTor OUTPUT INTO: http://msdn.microsoft.com/en-us/library/ms177564.aspx
您仍然需要两个INSERT语句，但听起来您想IDENTITY从第一个插入中获取并在第二个中使用它，在这种情况下，您可能需要查看OUTPUT或OUTPUT INTO：http: //msdn.microsoft.com/en-我们/图书馆/ms177564.aspx
回答by Bob Probst
It sounds like the Link table captures the many:many relationship between the Object table and Data table.
听起来链接表捕获了对象表和数据表之间的多：多关系。
My suggestion is to use a stored procedure to manage the transactions. When you want to insert to the Object or Data table perform your inserts, get the new IDs and insert them to the Link table.
我的建议是使用存储过程来管理事务。当您要插入对象或数据表时，执行插入操作，获取新 ID 并将它们插入到链接表中。
This allows all of your logic to remain encapsulated in one easy to call sproc.
这允许您的所有逻辑保持封装在一个易于调用的 sproc 中。
回答by devio
You might create a View selecting the column names required by your insert statement, add an INSTEAD OF INSERT Trigger, and insert into this view.
您可以创建一个视图，选择插入语句所需的列名，添加 INSTEAD OF INSERT 触发器，然后插入到此视图中。
回答by Craig
If you want the actions to be more or less atomic, I would make sure to wrap them in a transaction. That way you can be sure both happened or both didn't happen as needed.
如果您希望操作或多或少具有原子性，我会确保将它们包装在事务中。这样你就可以确定两者都发生了，或者两者都没有根据需要发生。
回答by Sergei Zinovyev
I want to stress on using
我想强调使用
SET XACT_ABORT ON;
for the MSSQL transaction with multiple sql statements.
用于具有多个 sql 语句的 MSSQL 事务。
See: https://msdn.microsoft.com/en-us/library/ms188792.aspxThey provide a very good example.
请参阅：https: //msdn.microsoft.com/en-us/library/ms188792.aspx他们提供了一个很好的例子。
So, the final code should look like the following:
因此，最终代码应如下所示：
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
回答by Carlton Jenke
Insert can only operate on one table at a time. Multiple Inserts have to have multiple statements.
Insert 一次只能对一张表进行操作。多个插入必须有多个语句。
I don't know that you need to do the looping through a table variable - can't you just use a mass insert into one table, then the mass insert into the other?
我不知道你需要通过一个表变量进行循环——你不能只在一个表中使用大量插入，然后在另一个表中使用大量插入吗？
By the way - I am guessing you mean copy the data from Object_Table; otherwise the question does not make sense.
顺便说一句 - 我猜你的意思是从 Object_Table 复制数据；否则这个问题没有意义。
回答by David Aldridge
Before being able to do a multitable insert in Oracle, you could use a trick involving an insert into a view that had an INSTEAD OF trigger defined on it to perform the inserts. Can this be done in SQL Server?
在能够在 Oracle 中执行多表插入之前，您可以使用一个技巧，包括插入到一个视图中，该视图定义了一个 INSTEAD OF 触发器来执行插入。这可以在 SQL Server 中完成吗？
回答by FakirPori
-- ================================================
-- 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
