0

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.

2

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.

  • Is there any need for me to reseed if I my new keys that I insert are negative numbers (to ensure there is no overlap with my existing keys)? Or once I turn IDENTITY_INSERT back off, it'll continue from the last MAX(value) seed? – J.D. Feb 6 at 22:39
  • Using IDENTITY_INSERT won't require you to reseed your identity column, it will pick up the new seed value even with the manual inserts. – HandyD Feb 7 at 5:34
  • @HandyD makes a good point, and yes no probs if you want to use negative values. I've updated my answer to reflect both those points. – Matthew McGiffen Feb 7 at 10:05
0

Adding one more aspect of answer by Matthew McGiffen, I would propose to do bcp out from source database tables to 4 files for each of these tables and then bcp in to tempdb of the destination database. These tables will be plain table i.e. without any referential integrity, afterwards you can define dependency on them in-line with source table and this will succeed as it was working at source database.

Adding clarification of using BCP instead of other methodologies is for the very simple reason that firewall will not be need to set if these servers are production and test. BCP has been faster for me in comparison to Export/Import and SSRS however thats my experience and is very much debatable.

Post successful completion of above, it would be easy to insert into the target table starting from the last table in the entity relationship map after enabling SET IDENTITY_INSERT {Target TableName} ON. Once this is done for all the tables and relationship is set, you should be good to go.

Hope above helps.

  • Just curious, why BCP as opposed to other data transfer methodologies? (Coincidentally BCP might make sense in my case because the data might be large.) – J.D. Feb 5 at 17:32
  • 1
    I prefer bcp instead of other methodologies for simple reason that firewall doesn't come in between and I don't have to run to network team to define rule for me specially between Production and Test. And bcp seems to have been faster than other methods(Export/Import) and SSIS however that is debatable. – Learning_DBAdmin Feb 6 at 5:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.