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 SELECT @newid = SCOPE_IDENTITY()
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!
- 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 USING ( SELECT @newid AS [NewId], [fields to be inserted], Id FROM Children WHERE ParentId = @id ) AS original ON 0=1 WHEN NOT MATCHED BY TARGET THEN 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:
DECLARE @map TABLE ( 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.