Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Copying parent-child rows and SQL Dependency Tracker

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.

 

Comments

Posted by Anonymous on 13 July 2011

Pingback from  Dew Drop &ndash; July 13, 2011 | Alvin Ashcraft&#039;s Morning Dew

Posted by Anonymous on 14 July 2011

Pingback from  Dew Drop &ndash; July 14, 2011 | Alvin Ashcraft&#039;s Morning Dew

Leave a Comment

Please register or log in to leave a comment.