I ran into a problem recently what required me to transverse a hierarchy and create a deep copy of the data in the tables. Expecting this to be a solved problem I turned to Google and was met by cursors. Many, many cursors.

I’m one of those people that was thought to never use a cursor and as a side effect I don’t really have much experience with them. So I went out to see if I could make a set operation as nesting cursors tends to end in tears. (or so I’ve been taught), so after some mulling I came up with a way to do it without a cursor.

Declare @Parrent TABLE( ID int PRIMARY KEY IDENTITY, Value nvarchar(50))
Declare @Child TABLE( ID int PRIMARY KEY IDENTITY, ParrentID int, Value nvarchar(50))

insert into @Parrent (Value) Values ('foo'),('bar'),('bob')
insert into @Child (ParrentID, Value) Values (1,'foo-1'),(1,'foo-2'),(2,'bar-1'),(2,'bar-2'),(3,'bob')

declare @parrentToCopy table (ID int) -- you can me this a collection
insert into @parrentToCopy values (2)

select * from @Parrent p inner join @Child c on p.ID = c.ParrentID order by p.ID asc, c.ID asc

DECLARE @Ids TABLE( nID INT);

INSERT INTO @Parrent (Value)
OUTPUT INSERTED.ID
INTO @Ids
SELECT 
		Value
FROM @Parrent p 
inner join @parrentToCopy pc on pc.ID=p.ID
ORDER BY p.ID ASC

INSERT INTO @Child (ParrentID, Value)
SELECT
       nID
       ,Value
FROM @Child c
inner join (select ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS 'RowNumber' from @parrentToCopy) o ON o.ID = c.ParrentID
inner join (select nID, ROW_NUMBER() OVER (ORDER BY nID ASC) AS 'RowNumber' from @Ids) n ON o.RowNumber = n.RowNumber

select * from @Parrent p inner join @Child c on p.ID = c.ParrentID order by p.ID asc, c.ID asc

This method is flat, no nesting and can be extended for deeper hierarchies. To do a self-referential hierarchy (parents can have parents) would require 2 steps, one to find all the nodes, a second to update the references. The crux is in the mapping of Id’s to Id’s.

inner join (select ID, ROW_NUMBER() OVER (ORDER BY ID ASC) AS 'RowNumber' from @parrentToCopy) o ON o.ID = c.ParrentID
inner join (select nID, ROW_NUMBER() OVER (ORDER BY nID ASC) AS 'RowNumber' from @Ids) n ON o.RowNumber = n.RowNumber

This breaks the problem open and gives you a guide to go to the next step whiteout losing information. If your Id’s are Guids, or non-sequential then you would have to add an ID column to

DECLARE @Ids TABLE( nID INT);

This column would then be used for the order to determine row numbers.