I have four tables in database A that have identity key columns and have foreign key references to each other on those columns (without actual foreign key objects created, just the columns themselves).
I need to move all the data from these tables to correlating (almost) identical tables in database B. (There's already data in the tables in database B too, so I'm trying to append this data.)
How can I move the data to database B which will generate new key values for each table's records but without disrupting the referential integrity.
Best Answer
As you're going to have new id values, it's not going to to be easy to do a straight copy.
I might suggest copying the data to a set of staging tables with no foreign keys defined. Then, in the staging tables, increase the existing ID values for all rows in a given table by a fixed amount that will guarantee they are higher than the max value in the destination tables (or you could change them to negative values if you haven't already used those) - you'll need to apply the same update to the foreign key fields in any child table at the same time.
Then you can use
SET IDENTITY_INSERT {TableName} ON;to allow you to insert the updated records to the destination table. You should turn that off again once you are done.SQL Server will automatically take account of the new rows you have inserted to re-seed the Identity values for new records moving forward so once you've inserted your records all is good.