Jamie Pollard Jamie Pollard - 7 months ago 13
SQL Question

Given a parent / child key table, how can we recursively insert a copy of the structure into another table?

I have a recursive CTE which gives me a listing of a set of parent child keys as follows, lets say its in a temp table called [#relationtree]:

Parent | Child
--------------
1 | 3
3 | 5
5 | 6
5 | 9


I want to create a copy of these relationships into a table with, lets say, the following stucture:

CREATE TABLE [dbo].[Relations]
(
[Id] int identity(1,1)
[ParentId] int
)


How can I insert the above records but recursively obtain the previously inserted identity value to be able to insert that value as the ParentId column for each copy of a child I insert?

I would expect to have at the end of this in [dbo].[Relations] (given our current seed value is, say 50)

Id | ParentId
-------------
... other rows present before this query ...
50 | NULL
51 | 50
52 | 51
53 | 51


I'm not sure that scope_identity can work in this situation, or that creating a new temp table with a list of new IDs and inserting identity columns manually is the correct approach?

I could write a cursor / loop to do this, but there must be a nice way of doing some recursive select magic!

Answer

Since you're trying to put the tree into a segment of the table it looks like you're going to need to use SET IDENTITY_INSERT ON for the table anyway. You're going to need to make sure that there is room for the new tree. In this case, I'll assume that 49 is the current maximum id in your table so that we don't need to be concerned with overrunning a tree that's later in the table.

You'll need to be able to map the IDs from the old tree to the new tree. Unless there's some rule around the ids, the exact mapping should be irrelevant as long as it's accurate, so in that case, I'd just do something like this:

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

I haven't tested that, but if it doesn't work as expected then hopefully it will point you in the right direction.

Comments