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