Return a source table column in an INSERT .. OUTPUT .. SELECT query

Question

I want to copy data between tables and build a mapping of old and new primary keys. I need the mapping to later update child tables.

What I want:

preamble

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)

code

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

Unfortunately this fails with The multi-part identifier "T1.ID" could not be bound..

Is there a way to select something else besides the inserted values into the OUTPUT? Or is there another way to do what I want?

Why not insert the existing IDs though? You can use [SET IDENTITY_INSERT ON](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15) to insert the existing IDs into the target table and turn it off afterwards — Panagiotis Kanavos, Mar 17 '22 at 13:45

score 1 · Accepted Answer · answered Mar 17 '22 at 13:54

INSERT OUTPUT can only output columns from the table you're inserting into. MERGE can output source tables as well, eg

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

Return a source table column in an INSERT .. OUTPUT .. SELECT query

1 Answers1

Related