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
CREATE TABLE [dbo].[Relations]
[Id] int identity(1,1)
Id | ParentId
... other rows present before this query ...
50 | NULL
51 | 50
52 | 51
53 | 51
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.