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

我的数据库包含三个名为Object_Table,Data_Table和的表Link_Table.链接表只包含两列,对象记录的标识和数据记录的标识.

我想复制从数据DATA_TABLE在那里它与一个给定对象的身份,并插入相应的记录到Data_Table和Link_Table一个不同的给定对象的身份.

我可以通过选择一个表变量并通过为每次迭代执行两次插入来循环来完成此操作.

这是最好的方法吗？

编辑:我想避免循环有两个原因,第一个是我懒惰,循环/临时表需要更多代码,更多代码意味着更多地方犯错误,第二个原因是关注性能.

我可以将所有数据复制到一个插入中,但是如何将链接表链接到每个记录都有新ID的新数据记录？

1> Joel Coehoor..：

---

在一个声明中:不.

在一次交易中:是的

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

好消息是上面的代码也保证是原子的,并且可以从一个客户端应用程序发送到服务器,在一个函数调用中只有一个sql字符串,就像它是一个语句一样.您还可以将触发器应用于一个表以获得单个插入的效果.但是,它最终仍然是两个语句,您可能不希望为每个插入运行触发器.

---

@Joel,很棒的问题.据推测,有人希望有另一种现实,你就是坏消息的承载者.;)

---

这并没有解决问题.他想插入从Object_Table读取的数据.即插入... select ...`语句.上面的代码如何读取或循环遍历Object_Table数据.然后,您仍然需要使用提问者不想做的表变量.

---

当然，这可以解决问题。也许我没有为此编写全部代码，但是OP也没有共享他想复制的所有列。此答案中演示的功能将使OP能够执行他的要求...运行查询以创建记录，获取新记录的ID，然后以原子方式将该ID用于第二条记录。OP已经知道如何进行插入/选择。这是他丢失的那一块。

2> Cade Roux..：

---

你仍然需要两个INSERT语句,但听起来你想要IDENTITY从第一个插入中获取并在第二个插入中使用它,在这种情况下,你可能想要查看OUTPUT或OUTPUT INTO:http://msdn.microsoft.com/en-我们/库/ ms177564.aspx

3> tpower..：

---

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

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使用临时表,然后使用普通插入完成.所以我可以避免我的循环,但我无法避免临时表.

4> Bob Probst..：

---

听起来像Link表捕获了Object表和Data表之间的许多关系.

我的建议是使用存储过程来管理事务.如果要插入Object或Data表,请执行插入,获取新ID并将其插入Link表.

这允许所有逻辑保持封装在一个易于调用的sproc中.