带ParentId的层级表数据复制问题：SQL同步父子关系失败求助

问题背景 #

你需要复制表中EnityId = 'me202'的所有记录，生成EnityId = 'me203'的新记录，同时完整保留原有的父子层级关系，但当前使用的SQL语句插入后，新记录的ParentId全部为null，无法正确关联父节点。

原表结构与示例数据 #

-- 原表初始数据
Id  ParentId  EnityId  CreatedTime
1   null      'me202'  20/1/2019 12:00:25
2   1         'me202'  20/1/2019 12:02:25
3   2         'me202'  20/1/2019 12:04:25
4   1         'me202'  20/1/2019 12:06:25

期望结果 #

-- 插入后的目标数据
Id  ParentId  EnityId  CreatedTime
1   null      'me202'  20/1/2019 12:00:25
2   1         'me202'  20/1/2019 12:02:25
3   2         'me202'  20/1/2019 12:04:25
4   1         'me202'  20/1/2019 12:06:25
5   null      'me203'  20/1/2019 12:00:25
6   5         'me203'  20/1/2019 12:02:25
7   6         'me203'  20/1/2019 12:04:25
8   5         'me203'  20/1/2019 12:06:25

原SQL的问题分析 #

你的原SQL尝试通过CreatedTime匹配新生成的父记录，但存在两个致命问题：

1. 时机错误：执行INSERT语句时，新记录还未写入表中，子查询根本找不到EnityId = 'me203'的记录，直接返回null。
2. 唯一性风险：CreatedTime并非唯一标识，即使后续查询也无法精准匹配到对应的父记录，容易出现关联错误。

解决方案：用映射表维护原Id与新Id的关联 #

我们可以通过表变量存储原记录的Id和新插入记录的Id的映射关系，分步骤插入根节点和子节点，确保父子关系准确同步。

具体SQL代码

-- 1. 创建表变量，存储原记录Id和新记录Id的映射关系
DECLARE @IdMap TABLE (OldId INT, NewId INT);

-- 2. 先插入所有根节点（ParentId为null的记录），并记录映射
INSERT INTO abc (ParentId, EnityId, CreatedTime)
OUTPUT inserted.Id, src.Id INTO @IdMap(NewId, OldId)
SELECT ParentId, 'me203', CreatedTime
FROM abc src
WHERE src.EnityId = 'me202' AND src.ParentId IS NULL;

-- 3. 循环插入所有子节点，通过映射表找到对应的新父Id
WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO abc (ParentId, EnityId, CreatedTime)
    OUTPUT inserted.Id, src.Id INTO @IdMap(NewId, OldId)
    SELECT map.NewId, 'me203', src.CreatedTime
    FROM abc src
    JOIN @IdMap map ON src.ParentId = map.OldId
    WHERE src.EnityId = 'me202'
    -- 避免重复插入已处理过的记录
    AND NOT EXISTS (SELECT 1 FROM @IdMap WHERE OldId = src.Id);
END

代码说明

• 映射表@IdMap：核心作用是建立原记录和新记录的唯一关联，确保我们能精准找到每个父记录对应的新Id。
• 分阶段插入：先处理根节点，再循环插入子节点，每次插入都通过映射表关联正确的父节点，直到所有层级的记录都被复制完成。
• @@ROWCOUNT：用来判断上一次插入是否有新记录，当没有更多子节点需要插入时自动退出循环，避免无效执行。

简化方案：用递归CTE处理层级关系 #

如果你的数据库支持递归CTE（比如SQL Server），可以用这种更简洁的方式，先梳理原数据的层级结构，再一次性完成插入：

DECLARE @IdMap TABLE (OldId INT, NewId INT);

-- 递归CTE获取原数据的完整层级结构
WITH OriginalHierarchy AS (
    SELECT Id, ParentId, EnityId, CreatedTime
    FROM abc
    WHERE EnityId = 'me202' AND ParentId IS NULL
    UNION ALL
    SELECT child.Id, child.ParentId, child.EnityId, child.CreatedTime
    FROM abc child
    JOIN OriginalHierarchy parent ON child.ParentId = parent.Id
    WHERE child.EnityId = 'me202'
)
-- 插入根节点和子节点，同时维护映射关系
INSERT INTO abc (ParentId, EnityId, CreatedTime)
OUTPUT inserted.Id, src.Id INTO @IdMap(NewId, OldId)
SELECT NULL, 'me203', src.CreatedTime
FROM OriginalHierarchy src
WHERE src.ParentId IS NULL
UNION ALL
SELECT map.NewId, 'me203', src.CreatedTime
FROM OriginalHierarchy src
JOIN @IdMap map ON src.ParentId = map.OldId;

这个方案通过递归CTE先理清原数据的层级依赖，再分根节点和子节点插入，同样借助映射表确保父节点关联正确。

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