返回插入中的源表列。输出..。选择查询

Question

我希望在表之间复制数据，并构建旧的和新的主键的映射。我需要映射，以便稍后更新子表。

我想要的：

序言

create table T1(Id int IDENTITY(1,1) primary key, X int, Y int, Z int)
create table T2(Id int IDENTITY(1,1) primary key, X int, Y int, Z int)

create table C1(Id int IDENTITY(1,1) primary key, T1_Id int not null foreign key references T1(Id), A int) 
create table C2(Id int IDENTITY(1,1) primary key, T2_Id int not null foreign key references T2(Id), A int)

代码

declare @keyMapping table(oldId int, [newId] int)

insert into T2 (X, Y, Z)
output INSERTED.ID, T1.ID into @keyMapping
select X, Y, Z from T1;


insert into C2 (T2_Id, A)
select km.newId, C1.A from C1
join @keyMapping km on C1.T1_Id = km.oldId

不幸的是，这在The multi-part identifier "T1.ID" could not be bound.中失败了。

除了插入到输出中的值之外，还有选择其他内容的方法吗？还是有别的方法来做我想做的事？

Answer 1

插入输出只能从要插入的表中输出列。合并也可以输出源表，例如

declare @keyMapping table(oldId int, [newId] int)

merge into T2 
using (select ID, X, Y, Z from T1) as src 
on 1=2
when not matched then insert (X,Y,Z) VALUES (src.X,src.Y,src.Z)
output inserted.ID, src.ID into @keyMapping(newId,oldId);