How to Merge two databases in one in SQL Server 2008?

Question

Hi

I have 2 PCs, each one of them has SQL Server 2008 installed on it and there is a database with data in it.

I need a way that I can move data in my DB from this SQL Server to another one (another PC which has the same DB)

• move data from one PC to another one -

There is one problem, the ID column, because each DB in my 2 PCs has data in it so this column counts from 1,2,3,....... ( data will be conflict with other data in my DB )

Is there any way to solve my problem and move data successfully?

Answer 1

Are those ID columns of type IDENTITY? If so, when you insert the new data from the second database, new ID's will be generated -> no clashes. This would then mean you might need to track the inserts, so that you can define what the old ID from DB#2 has been translated to when it was inserted into DB#1.

If these columns are not IDENTITY columns, you could just make sure to add a sufficiently large number to the old values, e.g. replace each ID (e.g. 5) with ID+100'000 = 100'005 --> thus you could avoid all clashes. Again, if you have any data referencing that main table in your DB#2, you would have to fix up the old references (the old foreign key ID's) to the new values, before inserting them into the table on your new DB.

It's a bit messy - but it can definitely be done if you think about it in enough detail. Basically, you will need to renumber the data being imported, and you need to make sure not to have any clashes. There's no functionality in SQL Server to help you with this very much - there's no "import this data and renumber its ID's" kind of command, unfortunately.