Merging 12 databases

Question

Merging 12 databases

Benjamin Wheatcroft

Newbie

Points: 5
More actions
February 16, 2004 at 5:05 am

#161624

Hello all,
I have a question regarding the merging of data using SQL server. I have 12 separate databases, all with the same referential structure and tables etc. but with different data in them. What I want to do is merge all the data from these 12 into 1 large database, again with the same structure. The tables within these databases comprise foreign keys from ids, and the 12 databases contain some duplicates of these keys, so I will need some method of re-assigning these ids during the merge to avoid conflicts.
So far I have looked at using DTS, and also just using queries but have had only limited success. An ideal outcome would be for me to create some kind of script for doing this as the merge would need to be completed on a regular basis.
Any help would be very much appreciated!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65143 More actions · Answer 1

I don't think what tool you use matters (although I'd prefer DTS) but the process you use to transfer the data. I have not done it personally but I believe in DTS you can set connection details at parameter level, so you can write one DTS and execute it once for each database.

Create a table to contain reassigned id's (oldid & newid)
Match prominent source table to destination to check if id already present, if so create a row in the 'reassigned id' table.
Update 'reassigned id' table with new id's
Transfer source tables to destination and use new id if necessary by matching id in 'reassigned id' table.

One thing you didn't mention was if the id's are IDENTITY.

Far away is close at hand in the images of elsewhere.
Anon.

Benjamin Wheatcroft Newbie Points: 5 More actions · Answer 2

Thanks very much for the help, the id's are not IDENTITY, just integers as far as I can tell.

I have been looking at implementing merge replication as this seems to be an ideal solution, but the matter of needing to transform the id's when replicating has thrown me. Do you perhaps know of any good resources on this subject?

Cheers,

Ben

David Burrows SSC Guru Points: 65143 More actions · Answer 3

Unfortunately no, sorry. I have no experience of Merge Replication, and when I did touch on Replication years ago, the problems I had put me off it for life. I think your solution should depend on how critical the timing needs to be. Why use replication when you only need to update once a day. Is it possible to change the 12 databases all to use a different range of ids? Then you would not have a problem when merging.

Far away is close at hand in the images of elsewhere.
Anon.

Benjamin Wheatcroft Newbie Points: 5 More actions · Answer 4

Yes I have already managed to implement a solution whereby 1000000 is added to all the id's from database 1, 2000000 to database 2 etc. but this is not ideal. However, it does work and looks like it may have to be the one I go with!

Thanks for the help,

Ben

Keith Howell SSC-Addicted Points: 439 More actions · Answer 5

My experience with merge replication is that you have to make sure the id's are uniquely allocated like you have already done. So the only benefit is that it does the replication all the time and not at the end of the day like you propose.