SQL通过SELECT插入行后将目标表新ID更新到源表的实现问题

解决方案 #

可以通过SQL Server的MERGE语句配合OUTPUT子句实现需求，核心逻辑是利用MERGE可以同时访问源表、目标表字段的特性，把插入生成的自增ID和原SourceTable的主键ID做映射，再回写更新，完全不受业务字段重复的影响。

完整实现代码 #

-- 声明表变量存储映射关系：SourceTable的Id <-> TargetTable新生成的Id
DECLARE @IdMapping TABLE (
    SourceId INT,
    NewTargetId INT
)

-- 用MERGE执行插入，同时生成映射关系
MERGE #TargetTable t
USING #SourceTable s
ON 1=0 -- 恒假条件，所有源表行都会走INSERT逻辑，等效于直接插入
WHEN NOT MATCHED THEN
    INSERT (Field1, Field2)
    VALUES (s.Field1, s.Field2)
OUTPUT s.Id, inserted.Id INTO @IdMapping(SourceId, NewTargetId);

-- 用映射表回写SourceTable的TargetTableId字段
UPDATE s
SET s.TargetTableId = m.NewTargetId
FROM #SourceTable s
INNER JOIN @IdMapping m ON s.Id = m.SourceId

-- 验证结果
SELECT * FROM #SourceTable

执行后#SourceTable输出结果 #

Id  Field1     Field2  TargetTableId
1   Apple      Pie     245
2   Chocolate  Cake    246
3   Apple      Pie     247

内容的提问来源于stack exchange，提问作者Measel