Solved

SQL Query - Query list of databases with identical structure and combine results

Posted on 2014-01-16
6
40 Views
Last Modified: 2016-03-28
I need to query multiple databases that contain the same structure and return the results into a single file.  I can do it with a lot of work by using UNION ALL between each select statement and fully defined db names in the FROM but given that only the DB name is changing between each query it seems like a script would be better in case there are any changes (e.g. adding another field).

Note: DB's are all on the same server

Can anyone provide advice on how to best accomplish this?

Thanks in advance.
0
Comment
Question by:-James-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39787105
Create a temp table in one of the databases. Then run an insert statement similar to this:
exec sp_msforeachdb "insert into TheDb..temptbl select * from ?..tbl"

Open in new window

to collect your data. If you want to add the actual db as a column, use '?' .
0
 
LVL 40

Expert Comment

by:lcohan
ID: 39830548
There are a few free tools like:

http://www.apexsql.com/promo/apexsql-diff_free-version.aspx?gclid=CPnLk7zesLwCFU5cfgod2y4AKA

http://dbcomparer.com/

And Microsoft SQL own (which I would recommend):
"SQL Server Data Tools (SSDT) includes a Schema Compare utility that you can use to compare two database definitions."
http://msdn.microsoft.com/en-us/library/hh272690(v=vs.103).aspx
0
 

Author Comment

by:-James-
ID: 39833384
Qlemo - If I understand sp_msforeachdb properly it is going to cycle through every db on the server.  I'm only looking to query from a set of financial databases (17 out of 30 + db's) .  Is there a way to identify the db names and then use a loop to cycle through the db's and union the results?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 39834222
You'll need to code that as an IF in the SQL executed, like in
exec sp_msforeachdb "if '?' like 'Finance%' insert into TheDb..temptbl select * from ?..tbl"

Open in new window

sp_msforeachtable support a where condition, but sp_msforeachdb does not, sadly.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41525187
I've requested that this question be closed as follows:

Accepted answer: 500 points for Qlemo's comment #a39834222

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question