How To Copy SQL Hierarchical Data At Run-time While Keeping Valid Internal References And Self Joins

Sometimes when you deal with hierarchical data structures you may need to perform internal copy operations. To imagine what I mean, you can keep up with the scenario illustrated below.

You have a "Departments" table which include all departments in your system. Each department should have a parent department except for the top department which has no parent.

Now, assume that at some point in your system you need to make duplicates of the existing departments and this should happen automatically at certain condition or at certain action triggered by system user. So, you need to write a stored procedure which will copy the existing departments in the "Departments" table and insert them in the same table.

So, you may think that it is just a simple INSERT-SELECT statement operating on the same table; "Departments" table. This will leads you to the result as in the image below.

Now, you should have a look on this image and re-think what you did, is this the result you wish to achieve?
If you don't know or you still think this is the right result, you can have a look on the image below.

As you see in the image above, the newly inserted departments are messed up regarding their parent departments IDs. This is because while copying the old departments and inserting the new ones you didn't calculate the new IDs of the parent departments so now each department has a parent ID referencing the old department not the appropriate newly created one. This is so wrong.

To understand what I mean, you can have a look on the image below.

As you can see the department which had ID equals to "1" should now have ID equals to "5". Also, the department which had ID equals to "2" should now have ID equals to 6" and so on........

So, the valid result you wish to achieve is as in the image below.

So, how to reach this result? This is the main question this article is trying to answer.

Steps

1. Declare a table variable in which we will keep the IDs mapping. Each record in this table will hold the old copied ID and its corresponding newly inserted ID. This way anytime we need to map an old ID to its new one we can use this table as a reference
2. Copy and insert departments one by one and for each insert you just copy the "ParentID" column value as it is and we will deal with it later to be updated with the right value. Also, for each insert, insert a record in the IDs mapping table to hold the old and new IDs
3. Update the "ParentID" column for the newly inserted departments with the new IDs depending on the IDs mapping table which now should be populated with IDs pairs

Now, it is the time for some code.

-- Variable to hold the ID of the department to be copied; the old department ID
DECLARE @OldDeptID INT

-- Variable to hold the ID of the newly copied department; the new department ID
DECLARE @NewDeptID INT

-- A table to hold the departments to be copied from the "Departments" table
-- The idx column is an identity column
DECLARE @DepartmentsToCopy TABLE (idx INT IDENTITY(1,1), ID INT, Name VARCHAR(100), ParentID INT)

-- A table to map each old copied ID to its new inserted ID
DECLARE @IdsMapping TABLE(Old_Id int , New_Id int)

-- A counter to be used in a loop
DECLARE @counter int
SET @counter = 1

-- Inserting the departments to be copied into the @DepartmentsToCopy table
-- Here we selected all records without any filtering but this can be modified
-- according to your business needs
INSERT INTO @DepartmentsToCopy
(
 ID
 , Name
 , ParentID
)
SELECT ID
, Name
, ParentID
FROM Departments

-- Looping on each department record in the @DepartmentsToCopy table to perform
-- the required actions on each record one by one
WHILE @counter <= (select max(idx) from @DepartmentsToCopy)
BEGIN
 -- Inserting a copy of the current department record in the "Departments" table
 -- but with adding the word "New" at the end of the "Name" column
 INSERT INTO Departments
 (
  ID
  , Name
  , ParentID
 )   
 SELECT TOP 1 ID
 , Name + 'New'
 , ParentID
 FROM @DepartmentsToCopy
 WHERE idx = @counter
 
 -- Setting the value of @NewDeptID with the scope identity
 -- in order to hold the ID of the newly inserted department record
 SET @NewDeptID = SCOPE_IDENTITY()
 
 -- Setting the value of @OldDeptID with the old copied ID
 SELECT TOP 1
 @OldDeptID = ID
 FROM @DepartmentsToCopy
 WHERE idx = @counter
 
 -- Inserting a record into the @IdsMapping table to hold the IDs mapping
 -- where the old id is @OldDeptID and the new one is @NewDeptID
 INSERT INTO @IdsMapping
 (
    Old_Id
  , New_Id
 )
 VALUES(@OldDeptID, @NewDeptID)
 
 -- Incrementing the counter to work on the next department record
 SET @counter = @counter + 1
END

-- Updating the ParentID column of the newly inserted departments
-- to match the new IDs using the @IdsMapping table which hold the IDs mapping
UPDATE Departments
SET ParentID = map.New_Id
FROM Departments AS Dept
INNER JOIN @IdsMapping AS newOnly
ON Dept.ID = newOnly.New_Id
INNER JOIN @IdsMapping AS map
ON Dept.ParentID = map.Old_Id

That's it. Hope you will find this helpful someday.