What is the most efficient way to combine multiple MS SQL databases into 1 database?

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...

Thank you.
Who is Participating?
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
Replication to a staging Database where that data is then fed into your reporting database would be a path that I would heavily consider.

Step 1) Define your Reporting database entities, etc.
Step 2) Create the data feeds/ replication to get the data to a staging area.
Step 3) Create the data feeds from the staging area into your reporting database.

If you do it properly, it could be a nearly real-time process.
Tyler LaczkoCommented:
You could do separate selects on each db and append the results together on the software side where you are controlling the data.
agoodaleAuthor Commented:
@xDJR1875 -

The client needs all the tables replicated.  They have over 1000 Crystal Reports written to the multi-company schema.  I can create views in each of the 60+ DBs that would add the company key to make it look like the previous schema.  The question is how to combine those views into the single DB?

Can you explain a little further how that replication might work?

For example let's look at just 1 view: "VIEW_A".  I would have 60+ copies of VIEW_A across the DBs.  How would I replicate those into the staging DB?  Wouldn't replication create 60+ objects there?

Once I have 60+ copies of VIEW_A in the staging DB how would replication combine those into 1 VIEW_A in the final reporting DB?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
No, in merge-replication, you can have multiple tables feeding into 1 table.
So you would create the replicated table and the 60+ tables would publish to it. (an example of how this is used is when a hand-held device is used to capture data and then syncronized with the master database. Multiple hand-held devices can sync to the same database using merge replication. )

This one table that contains your merge-replicated data could then be used as part of your reporting database or you could feed that into the shape you want into another database.

Review replication in the documentation, especially merge-replication and I think you will get a clearer picture of some of the things that are possible.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
agoodaleAuthor Commented:
no comment
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.