如何复制表以避免在SQL中出现游标？

如何复制表以避免在SQL中出现游标？

您可以将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