I was facing quite well-known issue today – I had to develop logic of duplicating (versioning) parent-child records. Because I was not fully familiar with database schema I simply didn’t know which entities I should involve. I am very visual person and that’s why I employed Red Gate’s tool called SQL Dependency Tracker which my boss bought me last month. It can display dependencies visually and helped me a lot today. I saw dependencies very fast, observe cardinalities, columns and quite quickly have rough idea what is logic behind it. Than I started to write SQL.
Only sensible way how to copy parent-child hierarchies I know is to use OUTPUT clause and place temporary column in affected parent table:
-- create sample parent-child tables CREATE TABLE ParentTable (Id INT IDENTITY, Version INT, TempId INT) CREATE TABLE ChildTable (Id INT IDENTITY, ParentId INT, Name VARCHAR(100)) -- here is version column redundant just for this sample1 -- insert sample data INSERT INTO ParentTable (Version) VALUES (1) INSERT INTO ParentTable (Version) VALUES (1) INSERT INTO ParentTable (Version) VALUES (1) INSERT INTO ParentTable (Version) VALUES (1) INSERT INTO ChildTable (ParentId, Name) VALUES (1,'I am 1st child record of parent no. 1') INSERT INTO ChildTable (ParentId, Name) VALUES (1,'I am 2nd child record of parent no. 1') INSERT INTO ChildTable (ParentId, Name) VALUES (2,'I am 1nd child record of parent no. 2') INSERT INTO ChildTable (ParentId, Name) VALUES (2,'I am 2nd child record of parent no. 2') INSERT INTO ChildTable (ParentId, Name) VALUES (3,'I am 1st child record of parent no. 3') INSERT INTO ChildTable (ParentId, Name) VALUES (3,'I am 2nd child record of parent no. 3') INSERT INTO ChildTable (ParentId, Name) VALUES (4,'I am 1st child record of parent no. 4') INSERT INTO ChildTable (ParentId, Name) VALUES (4,'I am 2nd child record of parent no. 4') -- display sample data as join between Parent and Child table SELECT p.Id ParentId, p.Version ParentVersion, p.TempId as VersionToParentId, c.Id ChildId, c.Name as ChildName FROM ParentTable p JOIN ChildTable c ON c.ParentId = p.Id -- here starts copy sql DECLARE @ParentsToCopy TABLE (Id INT) DECLARE @ParentsMapping TABLE (OldId INT, ClonedId INT) -- temp table holding pairs of old and new ids of parent entities -- parent Ids to copy INSERT INTO @ParentsToCopy VALUES (1),(2),(3),(4) -- Copy parents at first and save pairs of OldId and ClonedId INSERT INTO ParentTable (Version, TempId) OUTPUT inserted.TempId, inserted.Id INTO @ParentsMapping (OldId, ClonedId) SELECT Version+1, p.Id FROM ParentTable p JOIN @ParentsToCopy pc ON pc.Id = p.Id -- filter parent records to those I want to copy --now have in @ParentsMapping pairs of old and new id of ParentTable entity --let's insert child records by joining them to @ParentsMapping.OldId -- It finds children records which parents I've copied in previous step and use ClonedId as new ParentId for them INSERT INTO ChildTable (ParentId, Name) SELECT p.ClonedId, Name FROM ChildTable c JOIN @ParentsMapping p ON p.OldId = c.ParentId -- display sample data again SELECT p.Id ParentId, p.Version ParentVersion, p.TempId as VersionToParentId,c.Id ChildId, c.Name as ChildName FROM ParentTable p JOIN ChildTable c ON c.ParentId = p.Id
Main point of this script is that you get newly inserted IDs of parent records in @ParentsMapping table and than can use it as source for children records copy operation. But it is not possible without TempId column in ParentTable added only for copy purposes. Reason why you have to add TempId column to parent entity is in limitation of OUTPUT clause. You cannot use anything but only “inserted.*” columns or constants with OUTPUT clause. You cannot simply do this:
INSERT INTO <some_table> (some_columns)
OUTPUT inserted.*, ID_OF_PARENT_RECORD
INTO <temp_table>
SELECT ID_OF_PARENT_RECORD
FROM <another_table>You have to insert ID_OF_PARENT_RECORD column to ParentTable.TempId column and get it back to @ParentsMapping using “inserted.TempId”. That’s thing I really don’t like.
If you have better idea how to copy parent-child record (but as bulk operation not row by row) without altering parent entity, please let me know.
