SQL Server:是否可以同时插入两个表? [英] SQL Server: Is it possible to insert into two tables at the same time?

问题描述

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

My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.

我想从链接到一个给定对象标识的DATA_TABLE中复制数据，并为另一个给定对象标识将相应的记录插入Data_Table和Link_Table中.

I want to copy the data from DATA_TABLE where it is linked to one given object identity and insert corresponding records into Data_Table and Link_Table for a different given object identity.

我可以通过选择一个表变量并通过每次迭代进行两次插入来循环实现这一点.

I can do this by selecting into a table variable and the looping through doing two inserts for each iteration.

这是最好的方法吗?

编辑:出于两个原因，我想避免循环，第一个原因是我很懒，并且循环/临时表需要更多代码，更多代码意味着需要更多的地方犯错，并且第二个原因是对性能的关注.

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.

我可以在一个插入中复制所有数据，但是如何获得链接表以链接到新数据记录，其中每个记录都有一个新ID?

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?

推荐答案

以下内容使用表变量设置了我遇到的情况.

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 answer that pointed me towards the OUTPUT clause I can demonstrate a solution:

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

OUTPUT INTO子句不能打开 (主键的任一侧，外键 键)的关系

the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship

我仍然可以OUTPUT INTO临时表，然后以正常插入结束.所以我可以避免循环，但无法避免临时表.

I can still OUTPUT INTO a temp table and then finish with normal insert. So I can avoid my loop but I cannot avoid the temp table.

这篇关于SQL Server:是否可以同时插入两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！