Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

Ilija

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.