Copying Hierarchical Data using INSERT+MERGE+OUTPUT

Suppose you have a hierarchical data structure consisting of the tables

  • parent (Id)
  • children (Id, ParentId => parent)
  • grandchildren (Id, ParentId => children)

and you want to copy a parent record and all its children and grand-children.

You start out with

INSERT INTO parent (non-identity fields)
SELECT [non-identity fields]
FROM parent WHERE Id = @id

Next you want to copy the child records, and sketch something like this

INSERT INTO children (ParentId, [fields except identity])
SELECT @newid, [fields except identity]
FROM children WHERE ParentId = @id

That would work by itself, but not if you want to copy the grand-children, as you need not only the set of the newly generated identities, but also how they map to the original IDs.

So you try to add an OUTPUT clause

INSERT INTO children (ParentId, [fields except identity])
OUTPUT INSERTED.Id, [we need the original id here]
SELECT @newid, [fields except identity]
FROM children WHERE ParentId = @id

and you find that you can only have column names of the columns of the INSERT operation, but the original ChildId is not part of the Insert!

Fortunately I found help on this blog which suggests using MERGE OUTPUT for the child table:

  • We declare the data to be selected (and inserted) as sub-select statement of the MERGE USING clause.
  • The MERGE condition 0=1 means that records are inserted only.
  • The OUTPUT clause can refer to source AND target columns

The result looks like this:

MERGE Children
    SELECT @newid AS [NewId], [fields to be inserted], Id
    FROM Children
    WHERE ParentId = @id
) AS original
ON 0=1
    INSERT (ParentId, [fields to be inserted])
    VALUES ([NewId], [fields to be inserted])
OUTPUT inserted.Id, original.Id INTO @map

@map is a table variable containing the mappings of old and new IDs:

    Id INT,
    OriginalId INT

or whatever your Id datatype is.

The grand-children records can simply be copies using an INSERT statement, if no further detail tables exist:

INSERT INTO GrandChildren (ParentId, [fields to be inserted])
SELECT map.Id, [fields to be inserted]
FROM GrandChildren
INNER JOIN @map map ON GrandChildren.ParentId = map.OriginalId

If you have more than 3 levels to copy, you need one mapping table and one MERGE statement for each additional level.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.