Consolidate mssql databases into 1

Question

I need to consolidate 20 databases that have the same structure into 1 database. I saw this post: https://stackoverflow.com/questions/2537986/consolidate-data-from-many-different-databases-into-one-with-minimum-latency

I didn't understand all of this so let me ask like this: There are some table who have primary keys but don't have sourceID, example:

DataBase 1
AgencyID    Name 
1           Apple
2           Microsoft

Database 2
AgencyID   Name
1          HP
2          Microsoft

It's obvious that these two tables cannot be merged like this, it needs aditional column:

DataBase 1
Source     AgencyID    Name 
DB1        1           Apple
DB1        2           Microsoft

Database 2
Source     AgencyID   Name
DB2        1          HP
DB2        2          Microsoft

If this is the right way of doing this, can these two tables be merged in one database like this:

Source     AgencyID    Name 
DB1        1           Apple
DB1        2           Microsoft
DB2        1           HP
DB2        2           Microsoft

...and is it possible to do it with Transactional replication? Thanks in advance for the answer, it would be really helpful if I would get the right answer for this.

Ilija

score 1 · Accepted Answer · answered Aug 17 '11 at 20:19

I would actually do the consolidation using SSIS. It seems to me that would make the consolidation much easier. In the consolidated database, I would make the primary key of the tables a combination of the original key and SourceID. You could use Derived Column in SSIS to fill the SourceID based off of which database the data is being brought in from. Does this help?

Consolidate mssql databases into 1

1 Answers1