Merge 12 databases

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!!!

I'm no expert by far, but you could create a stored procedure using whatever SQL commands you have tried, that would allow it to be run whenever. Or you could even make a trigger, if the update needs to happen on firing of a certain event.

Just some thoughts, probably not all that helpful...

how many id numbers are actually in use in each table?

e.g. lowest id=1, highest id=67453

what this would show is that the ids are all under 100000

thus, in a SELECT statement, you could add 100000 to the ids of the first table, in another select you could add 200000 to the ids of the second table, etc.

then you can INSERT/SELECT all 12 query results together into a consolidated table, and none of the id numbers will collide

you could create the New Database

insert the first set of table in
for the second set and after just do a Insert with a where like this

insert (col1,col2)
select col1,col2
from tb_2
where tb_2.col1 not in (select Col1 from tb_new_all )

this way you'll only insert those record that are not already in the table