SQL Server中复制数据并修改控制组及关联新主键的问题

可以利用SQL Server的OUTPUT子句捕获插入新记录时生成的主键，同时建立旧主键与新主键的映射关系，以此关联两张表的复制操作。具体步骤如下：

1. 创建映射表存储新旧主键关联
   先声明一个表变量（或临时表），用来保存原Controls表的旧主键和新插入行的主键：

   DECLARE @ControlMap TABLE (OldPK_Controls INT, NewPK_Controls INT)
   

2. 插入新的Controls记录并捕获主键映射
   在插入Controls表时，通过OUTPUT子句将原记录的主键和新生成的主键存入映射表，支持单条或批量复制场景：

   INSERT INTO Controls(PK_ControlGroup, Description)
   OUTPUT source.PK_Controls, inserted.PK_Controls INTO @ControlMap(OldPK_Controls, NewPK_Controls)
   SELECT 2, Description 
   FROM Controls source
   WHERE source.PK_ControlGroup = 1 -- 匹配你要复制的原控制组记录
   

3. 插入Controls_Data记录并关联新主键
   利用映射表中的关联关系，将Controls_Data的旧主键替换为新生成的主键，同时修改控制组为2：

   INSERT INTO Controls_Data(PK_Controls, PK_ControlGroup, Data)
   SELECT 
       cm.NewPK_Controls,
       2,
       cd.Data
   FROM Controls_Data cd
   JOIN @ControlMap cm ON cd.PK_Controls = cm.OldPK_Controls
   WHERE cd.PK_ControlGroup = 1 -- 匹配原控制组的关联数据
   

方法优势 #

• OUTPUT子句能直接捕获INSERT操作生成的IDENTITY主键，避免了SCOPE_IDENTITY()仅能返回最后一个主键的局限性，适配单条或批量插入场景。
• 映射表建立了新旧主键的一一对应关系，确保Controls_Data的记录能准确关联到新生成的Controls记录。

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