SQL递归复制自引用表行并保留层级（兼容SQL Server/Oracle）

这个问题的核心难点在于保留原层级结构——直接批量插入会把所有复制节点挂到指定父ID下，彻底破坏原有的父子分支关系。我们得先建立原节点ID和新生成ID的映射关系，再按层级顺序插入子节点，才能完美复刻原结构。

整体思路 #

1. 递归抓取目标节点（指定ID+所有后代），记录每个节点的原ID、原父ID，以及它所属的顶级源节点（用来区分不同分支）。
2. 先插入顶级源节点到新的父ID下，捕获新生成的ID，建立「原ID→新ID」的映射表。
3. 按层级从高到低插入子节点，利用映射表把原父ID替换为对应的新ID，从而保留原层级的分支关系。

SQL Server 2008 实现 #

SQL Server 2008支持CTE递归查询，还能通过OUTPUT子句直接捕获插入后的新ID，刚好适配这个场景：

-- 1. 定义参数：要复制的原ID列表、新的顶级父ID
DECLARE @SourceIds TABLE (Id INT);
INSERT INTO @SourceIds VALUES (1), (4);
DECLARE @NewParentId INT = 10;

-- 2. 递归获取所有要复制的节点，标记层级归属
WITH ThingHierarchy AS (
    SELECT 
        Id AS OriginalId,
        IdParent AS OriginalParentId,
        randomtext,
        Id AS TopOriginalId -- 标记当前节点所属的顶级源节点
    FROM THING
    WHERE Id IN (SELECT Id FROM @SourceIds)
    UNION ALL
    SELECT 
        t.Id AS OriginalId,
        t.IdParent AS OriginalParentId,
        t.randomtext,
        h.TopOriginalId
    FROM THING t
    JOIN ThingHierarchy h ON t.IdParent = h.OriginalId
)
-- 3. 插入顶级节点，同步建立原ID与新ID的映射
SELECT * INTO #IdMap FROM (
    INSERT INTO THING (IdParent, randomtext)
    OUTPUT inserted.Id AS NewId, h.OriginalId, h.TopOriginalId
    SELECT @NewParentId, randomtext
    FROM ThingHierarchy
    WHERE OriginalId = TopOriginalId -- 只插入顶级源节点
) AS MapOutput;

-- 4. 按层级顺序插入子节点，确保父节点已存在
WITH ChildNodes AS (
    SELECT 
        h.OriginalId,
        h.OriginalParentId,
        h.randomtext,
        h.TopOriginalId,
        1 AS Level
    FROM ThingHierarchy h
    WHERE OriginalId != TopOriginalId -- 排除已插入的顶级节点
    UNION ALL
    SELECT 
        h.OriginalId,
        h.OriginalParentId,
        h.randomtext,
        h.TopOriginalId,
        c.Level + 1 AS Level
    FROM ThingHierarchy h
    JOIN ChildNodes c ON h.OriginalParentId = c.OriginalId
)
INSERT INTO THING (IdParent, randomtext)
SELECT m.NewId, c.randomtext
FROM ChildNodes c
JOIN #IdMap m ON c.OriginalParentId = m.OriginalId
ORDER BY c.Level; -- 按层级顺序插入，保证父节点先被创建

-- 清理临时表
DROP TABLE #IdMap;

Oracle 实现 #

Oracle没有OUTPUT子句，但可以用序列（假设表使用THING_SEQ生成ID）结合临时表存储映射关系，兼容旧版本：

-- 1. 定义参数：要复制的原ID列表、新的顶级父ID
DECLARE
    TYPE IdList IS TABLE OF INT;
    v_SourceIds IdList := IdList(1, 4);
    v_NewParentId INT := 10;
BEGIN
    -- 2. 创建会话级临时表存储ID映射
    CREATE GLOBAL TEMPORARY TABLE IdMap (
        OriginalId INT,
        NewId INT,
        TopOriginalId INT
    ) ON COMMIT PRESERVE ROWS;

    -- 3. 递归获取所有要复制的节点
    WITH ThingHierarchy AS (
        SELECT 
            Id AS OriginalId,
            IdParent AS OriginalParentId,
            randomtext,
            Id AS TopOriginalId
        FROM THING
        WHERE Id MEMBER OF v_SourceIds
        UNION ALL
        SELECT 
            t.Id AS OriginalId,
            t.IdParent AS OriginalParentId,
            t.randomtext,
            h.TopOriginalId
        FROM THING t
        JOIN ThingHierarchy h ON t.IdParent = h.OriginalId
    )
    -- 4. 插入顶级节点并记录映射关系
    INSERT INTO THING (Id, IdParent, randomtext)
    SELECT THING_SEQ.NEXTVAL, v_NewParentId, randomtext
    FROM ThingHierarchy
    WHERE OriginalId = TopOriginalId
    RETURNING Id, OriginalId, TopOriginalId BULK COLLECT INTO IdMap;

    -- 5. 循环按层级插入子节点，直到没有新节点可插入
    FOR i IN 1..100 LOOP -- 假设最大层级不超过100，可按需调整
        WITH ChildNodes AS (
            SELECT 
                h.OriginalId,
                h.OriginalParentId,
                h.randomtext,
                h.TopOriginalId
            FROM ThingHierarchy h
            JOIN IdMap m ON h.OriginalParentId = m.OriginalId
            WHERE h.OriginalId NOT IN (SELECT OriginalId FROM IdMap)
        )
        INSERT INTO THING (Id, IdParent, randomtext)
        SELECT THING_SEQ.NEXTVAL, m.NewId, c.randomtext
        FROM ChildNodes c
        JOIN IdMap m ON c.OriginalParentId = m.OriginalId
        RETURNING Id, OriginalId, TopOriginalId BULK COLLECT INTO IdMap;
        
        -- 没有新插入的节点时退出循环
        IF SQL%ROWCOUNT = 0 THEN
            EXIT;
        END IF;
    END LOOP;

    -- 临时表会话结束自动清理，可选手动删除
    DROP TABLE IdMap;
END;
/

关键注意事项 #

• 映射表是核心：必须记录原ID和新ID的对应关系，否则子节点无法找到正确的新父节点。
• 层级插入顺序：确保父节点先被插入，子节点才能正确关联（SQL Server靠CTE递归顺序，Oracle靠循环层级实现）。
• 兼容性调整：如果Oracle用12c+的IDENTITY列，可以把序列替换为DEFAULT，但为了兼容旧版本，优先用序列方案。

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