What is the most efficient way to combine multiple MS SQL databases into 1 database?
Posted on 2011-10-11
I have a client who's software vendor is switching from a single, multi-company, database to a single company database configuration. The end result for my client is going to be 60+ separate DBs with the same schema. They need a solution to combine these 60 DBs back into 1 DB for reporting. Their software vendor is not providing this solution for them.
Building UNION views is not an option. MS SQL doesn't like queries that UNION 60+ DBs. Performance with UNION views is also a problem.
In the past I have written queries that would build a multi-company DB on a nightly schedule. I'm curious if there is a more elegant solution using replication, or bulk-copy, etc...