您可以将output子句与merge语句一起使用，以获取源ID和目标ID之间的映射。
这是您可以测试的一些代码。我使用表变量而不是实际表。
设置样本数据：
-- @A and @B is the source tablesdeclare @A as table( id int, FK_A_B int, name varchar(10))declare @B as table( id int, visible bit)-- Sample data in @A and @Binsert into @B values (21, 1),(32, 0)insert into @A values (1, 21, 'n1'),(5, 32, 'n2')-- @C and @D is the target tables with id as identity columnsdeclare @C as table( id int identity, FK_C_D int not null, name varchar(10))declare @D as table( id int identity, visible bit)-- Sample data already in @C and @Dinsert into @D values (1),(0)insert into @C values (1, 'x1'),(1, 'x2'),(2, 'x3')
复制数据：
-- The @IdMap is a table that holds the mapping between-- the @B.id and @D.id (@D.id is an identity column)declare @IdMap table(TargetID int, SourceID int)-- Merge from @B to @D.merge @D as D -- Target tableusing @B as B -- Source tableon 0=1 -- 0=1 means that there are no matches for mergewhen not matched then insert (visible) values(visible) -- Insert to @Doutput inserted.id, B.id into @IdMap; -- Capture the newly created inserted.id and -- map that to the source (@B.id)-- Add rows to @C from @A with a join to-- @IdMap to get the new id for the FK relationinsert into @C(FK_C_D, name)select I.TargetID, A.name from @A as A inner join @IdMap as I on A.FK_A_B = I.SourceID
结果：
select *from @D as D inner join @C as C on D.id = C.FK_C_Did visible id FK_C_D name----------- ------- ----------- ----------- ----------11 11x111 21x220 32x331 43n140 54n2
