Replicating multiple databases into one database

I would like to replicate multiple databases on different servers into one master database.

I have 5 different instances on separate servers all running SQL 2008 R2. The schemas and structure are all the same but the data is different. I need to replicate all the data into one database on a single instance.
What would the best way going forward?

There's about 132 tables which need to be replicated. I tried creating publishers on each server and subscribing them to a single database on one instance but with no success: data is being over written and some servers are not replicating.

I probably have the wrong approach or messed up something along the way. Your assistance will be highly appreciated.

How do you manage the primary keys on those 5 instances? Unless you carefully planned this ahead so that each instance generate non-overlapping keys on each instance, on each table (sounds like you didn't) there is no way aggregate the data. See Managing Identity Columns for identity keys. For application generated key it gets more complicated as is the application responsibility to generate non-overlapping keys.

After you set up proper key management on each of the 132 table involved on each of the 5 instances, you can set up replication, making sure you apply the initial snapshot each time carefully not to overwrite existing data.

StackExchange Database Administrators Q#24255, answer score: 3