Bulk Translation Of Table Contents

Question

I'm currently performing a migration operation from a legacy database. I need to perform migration of millions of originating rows, breaking the original content apart into multiple destination parent / child rows.

As it's not a simple 1 to 1 migration and the the resulting rows are parent / children row based on identity generated keys, what's the best mechanism for performing the migration?

I'm assuming that I can't use bulk insert as the identity values for the child rows cannot be determined at the point of generating the script content? The only solution I can currently think of is to set the identity explicitly and then have a predetermined starting point for the import.

If anyone else has any input I'd appreciate the feedback.

Answer 1

Thanks for the suggestion but I'd prefer to produce a programmatic solution. I'm currently using Nant / CruiseControl to automate the tests and need something I can recreate on the fly based on the current live legacy content.

Answer 2

This is my standard approach:

• create your new data model
• pull the data into the new DB unchanged
• write (and run) a SQL script to perform the migration
• test
• (optional) drop the tables with the legacy data

You can get a long way towards migrating the data with plain SQL. For the case you described, you might not need to deal with a single Cursor to get it across.

Running the process in Query Analyzer (or an analog in your dbms), you'll have the advantage that you can wrap everything in a Transaction so that you can roll back if anything goes wacky along the way. Write it in little bits and test it in chunks, on your dev database. Once everything is working correctly, set the script loose on your production database.

Sorted.