How to copy an object tree to a database

Question

I have a pretty standard situation with relational data, in which there is a root entity (and a corresponding table) with child objects. These children have children's education, etc. Etc. For about 6 levels. At each level there are many children up to one parental relationship. I would like to write a procedure that effectively copies the root object and all its child objects (recursively copies child children), creating new entities for each along the path, saving each in its own corresponding table.

I know this can be done with nested cursors, but I don't want to do this. I know that there is a more elegant solution, I just need help in creating it. I have a feeling that the solution is to combine the OUTPUT clauses and the MERGE statements.

If you could, please attach your answer to the developer level at the beginner level. I will need an explanation or an explanation link for any structure you use that is outside of the main SELECT INSERT UPDATE and DELETE.

Thank you for your time.

Answer 1

You need to use a generic table expression. Check this out: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Answer 2

I assume that you want to copy a subset of the data in the table hierarchy. By hierarchy, I mean tables that are interconnected through foreign keys in the obvious sense of the word. For example, Customers will be the root table, Orders child and OrderDetails another child (at 3rd level).

First, we copy the root table of the hierarchy:

 MERGE RootTable as target USING ( SELECT * FROM RootTable WHERE SomeCondition ) AS src ON 1=2 -- this is so that all rows that do not match will be added WHEN NOT MATCHED THEN INSERT (AllColumns) VALUES (AllColumns) OUTPUT src.ID as OldID, INSERTED.ID as NewID INTO #RootTableMapping 

Now we have a 1 to 1 mapping of the copy source and copying the target identifiers of the root table to #RootTableMapping . In addition, all root lines were copied.

Now we need to copy all the child tables. Here's a statement for one:

 MERGE ChildTable as target USING ( SELECT *, #RootTableMapping.NewID AS NewParentID FROM ChildTable JOIN #RootTableMapping ON ChildTable.RootID = #RootTableMapping.OldID WHERE SomeCondition ) AS src WHEN NOT MATCHED THEN INSERT (AllColumns, RootID) VALUES (AllColumns, NewParentID) 

Here we get for each child row the identifier of the cloned row of the root table so that we can bind the hierarchy. For this we use #RootTableMapping . We copy all unmodified columns, with the exception of the parent ID, which we substitute using NewID .

You will need one such MERGE for each child table. The concept also extends to hierarchies with more than two levels by adding additional joins. All levels, except the lower level, must record the mapping of the identifiers of the source code of the copy to the identifiers of the copy, so that the next level below can receive new identifiers.

Feel free to ask additional questions if I don't make everything clear enough. I know this is a rough sketch.