Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-16
6
Medium Priority
?
51 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 71

Accepted Solution

by:
Qlemo earned 2000 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 71

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 71

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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

662 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