t sql replicate rows in cascading tables

Question

I have a situation in which I have several related/cascaded tables. Lets say all 1-to-many relationships cascading down table1, table2, table3, table4, etc. What I have are default rows in the tables. They start with 1 record in table1 and have 1 or more related records in other tables.

What I am looking for is an easy way to replicate the rows and create/maintain new primary keys for each. By maintain I mean, table1.ID1 is replicated, so now there is a table1.ID2 with all the rest of the columns being the same, only the ID has changed.

On top of that all its related rows in all the other tables are replicated and the foreigns keys/primary keys all point to the new ones.

So table2 would have a foreign key of table1.ID2 a primary key of say table2.ID#, and the rest of the columns in the table2 row being the same as the row replicated in table2.

I am being lazy and trying to get out of having to manage the ID's and creating a very long Stored Procedure.

I don't think this can be done, but am hoping I am wrong. Thanks in advance.

Answer 1

We had to do this for some functionality in one our projects recently.

We ended up doing it using a recursive stored procedure. Each call needed two pieces of information:

• The name of the table where the records are being duplicated
• The primary key of the parent table of the table being duplicated

So if you had two tables A and B, where A.aid is the primary key of A, and B.aid references it, you would need to do the following in pseudocode:

Function deep_copy (table, parent)
  For each record in table whose parent is parent
    Create a new PK and copy this record
    For each subtable of table
      Call self with parameters (subtable, newPK)

You'd call deep_copy on each record of A you want to copy. Then deep_copy would call itself on the records in B that need to be copied, and any other sibling tables to B. The catch is you need the metainformation to know that B is a child of A. We happened to have that metainformation stored already so we didn't have to do anything special to acquire it.

Which ends up not being a very big stored procedure. If your PKs are all identity columns it makes it a whole lot easier. It branches out a hell of a lot, though. Not exactly an efficient algorithm. But then again it's an inherently complex problem for which relational data models aren't designed to deal with gracefully.

Answer 2

Triggers were created for exactly this. However , good triggers are difficult to write and a pain to maintain. But if you get them right, it is easy to implement the scenario you describe.