Consolidate SQL Server databases into 1

Question

I need to consolidate 20 databases that have the same structure into 1 database. I saw this post: 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

Answer 1

I solved the problem. Now I am using Transactional Replication. In "Publication Properties > Article Properties" I have to set "Action if name is in use" flag to "Keep existing object unchanged". Default is "Drop existing object and create a new one". In SQL 2008 even when I change table scheme these changes are applied to consolidation database.