如何复制数据库表记录并映射跨表关联关系？

针对复制Thing表指定记录到新分组，同时同步关联Attribute数据的需求，由于Thing_id是自增（Identity）列，核心难点是建立旧ID与新生成ID的可靠映射，以下是最优实现方案：

首选方案：使用MERGE语句生成ID映射 #

普通INSERT语句的OUTPUT子句无法直接获取源表的旧ID，而MERGE语句可以同时访问源表和插入后的目标表字段，完美解决映射问题，且无需依赖业务字段唯一性。

完整代码实现 #

-- 1. 声明临时表存储新旧Thing_id的映射关系
DECLARE @thing_map TABLE (Old_Thing_id INT, New_Thing_id INT);

-- 2. 通过MERGE插入主表记录，同时生成映射
MERGE INTO Thing AS target
USING (
    -- 筛选需要复制的源记录，包含旧ID
    SELECT Thing_id, Name, Desc, <new_group_id> AS Group_id
    FROM Thing
    WHERE Thing_id IN (<existing_thing_ids>)
) AS source
ON 1 = 0 -- 强制触发INSERT逻辑（永远不匹配）
WHEN NOT MATCHED THEN
    INSERT (Name, Desc, Group_id)
    VALUES (source.Name, source.Desc, source.Group_id)
-- 将源表旧ID和新生成的ID写入映射表
OUTPUT source.Thing_id AS Old_Thing_id, inserted.Thing_id AS New_Thing_id
INTO @thing_map(Old_Thing_id, New_Thing_id);

-- 3. 基于映射表复制关联的Attribute记录
INSERT INTO Attribute (Thing_id, Att_Name, Att_Desc)
SELECT t.New_Thing_id, a.Att_Name, a.Att_Desc
FROM Attribute a
INNER JOIN @thing_map t ON a.Thing_id = t.Old_Thing_id;

方案优势 #

• 绝对可靠：完全依赖自增ID映射，不受业务字段（Name/Desc）重复的影响
• 性能高效：仅需两次写入操作，逻辑简洁无冗余查询
• 兼容性广：支持SQL Server 2008及以上版本，适配绝大多数生产环境

替代方案（版本受限场景） #

如果无法使用MERGE（如旧版本SQL Server），可临时添加字段实现映射，但操作繁琐，仅作备选：

代码示例 #

-- 临时添加存储旧ID的字段（需表结构修改权限）
ALTER TABLE Thing ADD Old_Thing_id_Temp INT;

DECLARE @thing_map TABLE (Old_Thing_id INT, New_Thing_id INT);

-- 插入主表时同步写入旧ID到临时字段
INSERT INTO Thing (Name, Desc, Group_id, Old_Thing_id_Temp)
SELECT Name, Desc, <new_group_id>, Thing_id
FROM Thing
WHERE Thing_id IN (<existing_thing_ids>);

-- 生成映射表
INSERT INTO @thing_map
SELECT Old_Thing_id_Temp, Thing_id
FROM Thing
WHERE Old_Thing_id_Temp IN (<existing_thing_ids>);

-- 清理临时字段（可选：清空值或直接删除字段）
UPDATE Thing SET Old_Thing_id_Temp = NULL WHERE Old_Thing_id_Temp IS NOT NULL;
-- ALTER TABLE Thing DROP COLUMN Old_Thing_id_Temp;

-- 复制关联Attribute记录
INSERT INTO Attribute (Thing_id, Att_Name, Att_Desc)
SELECT t.New_Thing_id, a.Att_Name, a.Att_Desc
FROM Attribute a
INNER JOIN @thing_map t ON a.Thing_id = t.Old_Thing_id;

方案缺点 #

需要修改表结构，高并发场景下可能存在冲突，仅在MERGE不可用的情况使用。

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