Query multiple DB's

Question

Post reply

Query multiple DB's

steve.treloar

SSC Veteran

Points: 222
More actions
May 18, 2010 at 12:23 pm

#222705

Query multiple DB's and send cumulative result to text file, prepending the data.
Use DBname1
Select ColumnName from Table
Use DBname2
Select ColumnName from Table
Use DBname2
Select ColumnName from Table
But I want the result to be:
"DBnameX"\Result of cumulative resultset written to text file.
I'd like to string each of the queries together, then have the result set written to file but if each query needs to be a separate operation that appends to the file as it runs, that would be fine too.
I've got this part working:
Select 'DBname\' + ColumName from TableName
I'd like to do this:
Select 'DBname\' + ColumName from TableName >>text file
Or, I'd really like to do this:
Select 'DBname1\' + ColumName from TableName, 'DBname2\' + ColumName from TableName, 'DBname3\' + ColumName from TableName >>text file
I haven't figured out how to use QueryOut or Into yet.
Any insight for querying multiple databases and sending the cumulative result to one data file will be appreciated.
-Steve

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

declare a table variable and input the results into the table. then output the table variable.

Declare @tmptbl table columnname varchar(xx)

Insert into @tmptbl

select columnname from table1

Insert into @tmptbl

select columnname from table2

Insert into @tmptbl

select columnname from table3

select * from @tmptbl

you then can output @tmptbl to the output file.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

steve.treloar SSC Veteran Points: 222 More actions · Answer 2

So, you're saying to create a temporary table, but in what DB? Then query the individual DB/Tables, send the result set to the temporary table / DB, then query the tmptbl.

Problem that I don't understand with your suggestion is that I'm querying like table names from multiple DB's.

"Declare @tmptbl table columnname varchar(xx)" = syntax error near columnname

Lowell SSC Guru Points: 323518 More actions · Answer 3

three part naming conventions is how i often do it:

Select ColumnName from DBname1.dbo.Table

UNION ALL

Select ColumnName from DBname2.dbo.Table

UNION ALL

Select ColumnName from DBname3.dbo.Table

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dan.Humphries SSChampion Points: 12125 More actions · Answer 4

It depends on your needs.

I assume you are new to SQL but the temp table is not a table at all. it is a variable that only exists in memory. you would add the selection of information for each table and then select all information from the variable table.

The syntax you refer to is becuase my code is a sample only. you will have to make it fit your needs. for example the xx would need to be replaced with the size you need. do you need 10 characters 100 characters?

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

steve.treloar SSC Veteran Points: 222 More actions · Answer 5

Use "WSS_ MSWSS-1_EESP_CONTENT"

select 'EESP\' + FullUrl from dbo.webs

Use "WSS_ MSWSS-1_VPBFA_CONTENT"

select 'VPBFA\' + FullUrl from dbo.webs

Use "WSS_MSWSS-1_BOT_Content"

select 'BOT\' + FullUrl from dbo.webs

Where as, the return is: (extremely condensed)

EESP\directory

--------------

VPBFA\sites/HR/CrossFunctional/Summit 2010

--------------

BOT\collab

--------------

All in separate returns.

I'm actually querying 30+ DB's for the same information. The objective is to develope a csv for a .net app to build a dynamic menu for all of the SharePoint sites and sub site collections in our organization.

Getting the data is not the problem. Getting the result into a file/format usable as information is my trouble.

-Steve

malamal007 Grasshopper Points: 11 More actions · Answer 6

I thought it couldn't be done. I'd searched for it across the web. Terms such as mysql query multiple databases turned up nuthin' - that's right, not just nothing, but _flat-out_nuthin'_.

Today I was speaking with Matt Westgate of Lullabot about the challenges of managing 60+ Drupal web sites on a single codebase with each site having its own database, and Matt (who has a Drupal book coming out) mentioned liking MySQL for the fact that you could query multiple databases with a single query.

Jeep Dispatcher Parts