Copying data with foreign keys and/or identity columns

In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.

Somehow, that makes me a happier developer.

Anyway, one common problem that you’ll stumble across is the need to extract, version, and deploy data from one environment to another. This could for instance be meta-data that you want to sync from your production database to your test/dev database or the other way. In that process, a few requirements typically come up:

• Sometimes the existing data in the target table(s) needs to be deleted first,
• Any delete or insert operations (across multiple tables) need to happen in the correct order – so the foreign key constraints aren’t violated,
• Using IDENTITY_INSERT solves a few problems but creates others, so if you don’t use IDENTITY_INSERT, you’ll want to make sure that all those pesky identity columns in the target tables don’t mess up your foreign keys,
• And, of course, all of this has to happen atomically. All or nothing.

So that’s the essence of what my stored procedure LoadTableBlobs does – script one or more tables using FOR XML AUTO with a SELECT statement into a single XML blob. That XML blob is then passed to the stored procedure that will magically insert everything into the correct tables in a given target database.

Here’s an idea of how you might use it: In the source database, run something like this:

--- 1. Collect one or more tables' contents as a XML AUTO blob:
DECLARE @xml xml=CAST((
    SELECT * FROM dbo.A FOR XML AUTO, TYPE) AS varchar(max))+CAST((
    SELECT * FROM dbo.B FOR XML AUTO, TYPE) AS varchar(max))+CAST((
    SELECT * FROM dbo.C FOR XML AUTO, TYPE) AS varchar(max));

The resulting XML blob may look something like this:

<dbo.A a="5" name="five" />
<dbo.A a="4" name="four" />
<dbo.A a="1" name="one" />
<dbo.A a="3" name="three" />
<dbo.A a="2" name="two" />
<dbo.B b="5" a="1" name="five" />
<dbo.B b="10" a="2" name="five" />
<dbo.B b="15" a="3" name="five" />
<dbo.B b="20" a="4" name="five" />
...

Each element represents a single row; the element’s name is the name of the table and all the attributes are column values. NULL values are just left out of the element entirely. The relations between tables are declared in the DDL of the target database, so make sure you have proper foreign key constraints in place where you need them.

Now, move that XML blob any way you want to the destination database, and feed it into the LoadTableBlobs procedure, like so:

--- 2. Then, on the target database (with identical, existing tables),
---    run the procedure, passing the XML blob as argument:
EXECUTE dbo.LoadTableBlobs
    @blob=@xml,
    @use_identity_insert=0,  --- If you need IDENTITY_INSERT ON
    @truncate_table=0,       --- If you want to empty the target table(s) before inserting
    @print_only=0            --- If you want a "dry run" that only prints the T-SQL code

If you’re using IDENTITY_INSERT, you may just as well want to implement this using something like an SSIS package. But if you’re not, a neat trick that LoadTableBlobs does is that it manages your inserts so they happen in the correct dependency order, and it keeps track of identity columns (you’ll need a unique index on one or more non-identity columns, a natural key if you will). So even though your insert generates new identity values in the target tables, this is mapped, so everything is still glued together the way you’d expect.

I’ll probably follow up this post with more details on the inner workings of the procedure some day.

Get the script on the Downloads page, have fun with it somewhere safe (please test everything, don’t just take my word for it!) and let me know in the comments or on social media what you think of it.