Merge 2 seperate databases into one

[niall5098]

Hi There,

the situation i have is that i have 2 separate databases (on separate sites) and 2 of the same application as the front end. each database has the same objects, tables, procedure etc but different data. the request has come in to merge the databases into one but i am not sure how this will work as the same schema and objects exist in both. anyone have any idea how this can be achieved?

Regards,

Niall

 

[SQLScholar]

You cannot neccessarily merge them into one database easily.

It depends on the structure. For instance if one table (on both DB`s) has a primary key that is an ever increasing integer (a common PK) then they would have overlapping PK. The only option then would be to reassign the primary keys, but then you would have the issue that they may be referenced elsewhere as foriegn keys. All in all it could be very messy.



----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[Thargy]

Niall,

I've had to do precisely this in Oracle, but the method works in SQL Server.

First of all, if you don't have integer surrogate primary keys then add them to all your tables, in both databases.
Pick one database and multiply all its PK values by 10, and add 1. That means that every primary and foreign key in the db ends with a 1.

In t'other one, multiply every PK by 10 and add 2, which guarantees that every PK ends in a 2.

Put all the data from 2 into 1, secure in the knowledge that a PK collision is impossible.
Note that this also makes it possible to know which data came from which db (permanently).

Make sure that the next autonum (or whatever method you're using to generate PK values), is greater than the biggest number in each affected table.

Regards

T

 

[SQLScholar]

The above is a perfectly good way of doing it - however you need to make sure you do the same to all foriegn keys also.

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[niall5098]

thanks for the ideas. thargys solution might work with update cascade?

 

[Thargy]

Niall,

first of all, and to be clear about sql scholar's response, I assumed that you would understand that since every foreign key references a PK (or in Oracle a column guaranteed to be unique) that this automatically included FK's.
However, I didn't explicitly say that, so kudos to SQLScholar for that.

Update cascade is usually a world of trouble. I don't know enough about SQL Server to say whether or not it has such a feature, but if it does, it might be useful.
However, if you create some TSql to go through each table and do the deed, there's no need for such a thing.

I know (from personal experience) that this works, so I suggest you stick with the stored procedure approach, and forget cascade update(if such a feature exists).

Regards

T