Copying rows in a database when rows have children

Question

I need to make a copy of a row in a table and also copy all the rows that link to it via its foreign key in other tables. And then copy all the rows that link to these rows.

The problem is when I make a copy of the row the key is going to change. How can I propagate these changes through the relationships between tables?

Answer 1

A bit of a 'hack', but I often add a column 'copy_of_id', so that I can store the old id, and then copy over all rows in dependent tables for the newly created value.

It ain't pretty, but has advantages (human tracebility of what's happening, for instance).

You can also use a helper table for this, of course. Containing old ID and new ID columns. That will not pollute your primary table.

Answer 2

You can write a stored procedure which implements all the copy logic.

Essentially:

1. Insert copy row in master table - store new ID in variable
2. Insert copy of each row in child tables, referencing FK in variable. Store the ID of the child row in variable2
3. Insert new rows in tables dependent upon the child table, referencing the FK in variable2.

In short, write a stored proc that starts at the top and walks down as many tables as needed.

Answer 3

When creating your new "parent row", @@IDENTITY and SCOPE_IDENTITY() will contain the Id of your new header.

Then you can use them to create your "child rows", such as

INSERT INTO parent_table (parent_field1, parentfield2) VALUES('Some stuff', 'Other stuff');

INSERT INTO child_table (parent_id, other_field) VALUES(SCOPE_IDENTITY(), 'Etc.')

Take a look on MSDN for @@IDENTITY and SCOPE_IDENTITY() for code examples.