How to move data between multiple database's table while maintaining foreign-key relationships/referential integrity?

Question

I'm trying to figure out the best way to move/merge a couple tables of worth of data from multiple databases into one.

I have a schema similar to the following:

CREATE TABLE Products(
    ProductID int IDENTITY(1,1) NOT NULL,
    Name varchar(250) NOT NULL,
    Description varchar(1000) NOT NULL,
    ImageID int NULL
)

CREATE TABLE Images (
    ImageID int IDENTITY(1,1) NOT NULL,
    ImageData image NOT NULL
)

With a foreign-key of the Products' ImageID to the Images' ImageID.

So what's the best way to move the data contained within these table from multiple source databases into one destination database with the same schema. My primary issue is maintaining the links between the products and their respective images.

Answer 1

In this case you could move the images and then move the products. This would ensure that any image a product has a reference to will already be in place.

Answer 2

In SQL Server, you can enable identity inserts:

SET IDENTITY_INSERT NewTable ON
<insert queries here>
SET IDENTITY_INSERT NewTable OFF

While idenitity insert is enabled, you can insert a value in the identity column like any other column. This allows you to just copy the tables, for example from a linked server:

insert into newdb.dbo.NewTable
select *
from oldserver.olddb.dbo.OldTable

Answer 3

I preposition the data in staging tables (Adding a newid column to each). I add a column temporarily to the table I'm merging to that is Oldid. I insert the data to the parent table putting the currect oldid inthe oldid column. I use the oldid column to join to the staging table to populate the newid column in the staging table. Now I have the New FK ids for the child tables and ccan insert using them. If you have SQL server 2008, you can use the OUTPUT clause to return the old and newids to a temp table and then use from there rather than dding the column. I prefer, to have the change explicitly stored ina staging table though to troubleshoot issues in the conversion. At the end nullout the values inteh oldid column if you are then going to add records from a third database or drop it if you are done. Leave the staging tables in place for about a month, to make research into any questions easier.