问给定父/子密钥表，我们如何递归地将结构副本插入到另一个表中？

EN

SET IDENTITY_INSERT dbo.Relations ON

;WITH CTE_MappedIDs AS
(
    SELECT
        old_id,
        ROW_NUMBER() OVER(ORDER BY old_id) + 49 AS new_id
    FROM
    (
        SELECT DISTINCT parent AS old_id FROM #relationtree
        UNION
        SELECT DISTINCT child AS old_id FROM #relationtree
    ) SQ
)
INSERT INTO dbo.Relations (Id, ParentId)
SELECT
    CID.new_id,
    PID.new_id
FROM
    #relationtree RT
INNER JOIN CTE_MappedIDs PID ON PID.old_id = RT.parent
INNER JOIN CTE_MappedIDs CID ON CID.old_id = RT.parent
-- We need to also add the root node
UNION ALL
SELECT
    NID.new_id,
    NULL
FROM
    #relationtree RT2
INNER JOIN CTE_MappedIDs NID ON NID.old_id = RT2.parent
WHERE
    RT2.parent NOT IN (SELECT DISTINCT child FROM #relationtree)

SET IDENTITY_INSERT dbo.Relations OFF

CREATE TABLE #relationtree (
    Parent INT,
    Child INT
)

CREATE TABLE #relations (
    Id INT IDENTITY(1,1),
    ParentId INT
)

INSERT INTO #relationtree (Parent, Child) VALUES(1,3), (3,5), (5,6), (5,9)

INSERT INTO #relations (ParentId) values(1), (3), (5)

DECLARE @offset INT = IDENT_CURRENT('#relations')

;WITH relationtreeids AS (
    SELECT *,
           ROW_NUMBER() OVER(ORDER BY Parent, Child) - 2 AS UnmodifiedParentId -- Simulate an identity field
    FROM #relationtree
)
INSERT INTO #relations
-- The LAG window function allows you to inspect the previous row
SELECT  CASE WHEN LAG(Parent) OVER(ORDER BY Parent) IS NULL 
                THEN NULL
             WHEN LAG(Parent) OVER(ORDER BY Parent) = Parent 
                THEN UnmodifiedParentId + @offset ELSE UnmodifiedParentId + @offset + 1 
        END AS ParentId
FROM relationtreeids

Id  ParentId
1   1
2   3
3   5
4   NULL
5   4
6   5
7   5