How To Copy SQL Hierarchical Data At Run-time While Keeping Valid Internal References And Self Joins
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.
- 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
- 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
- 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.