Here's the thing, I have been running identical copies of my website for two different geographical regions. The site now supports multi-currency pricing so I want to merge both sites together.
All tables that I need to merge are Identity based and foolishly the matching tables in each copy of the database were seeded at the same number.
Is there a way to copy the data from one database to another at the same time giving the copy data new identity values that also are reflected in the foreign key values of referenced tables?
i.e.
Account
- Id
- Email
- FirstName
- etc
AccountImage
- Id
- Account_id
- FileName
- etc
And so on..
Best Answer
Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up. If that is the case then try this.
As long as you are consistent in the value you are adding all of your relationships will stay the same into the combined DB. So for example
You have an employee table with a current max id of 200,000 in DB A and 1,400,000 in DB B. You decide to move the data from DB A to DB B because it means moving less data. As you move your employee table you add 2,000,000 to the Employee_Id column.
And last piece of advice back up everything before you start in case you make a mistake :)