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

database, foreign-keys, sql

Solution

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

Problem

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.

Original source