Merge data from multiple databases into single database

• jamesdimauro

  SSC Enthusiast

  Points: 181

  More actions

  September 13, 2016 at 7:48 am

  #313050

  I have a number of regional databases. For now lets say there are 10 database. I have been challenged to merge the data from these databases into a single database. The schema's of the regional databases are identical, but obviously have different data. The new database that will hold all the data does not have to have the same schema. The regional databases will be hosted on a central sql server. For now there is only one sql server for the regional. the new database for now will also be on this same server.

  My initial plan is to take make a copy of one of the regional databases, remove all data, all fk constraints, all pk etc and add a column to each table that will be a database ID column. Then script a nightly merge. The merge would be consist of updates, inserts and deletes. There are 750 tables in the regional databases. Although the data will be relatively small for the next few years, (each table well under 200k rows and most less than 10k), i fear performance issues.

  I am not saying my strategy is the best. In fact I do not like it, but my strategy I hope explains the problem.

  Are there any automated tools? What about replication? Can i setup replication to insert a db id into a column that only exists in the destination\subscription database?

  Any ideas about the scenario are welcome!