SQL > Excel x2 on 250 databases.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL > Excel x2 on 250 databases.

Author

Topic

RobertStewart
Starting Member

7 Posts

Posted - 2011-05-18 : 12:07:32

Hi all,
I dont usually stray into the realms of SQL, but I have set up a SQL Server (MS SQL Server 2008) and I have got that up and running... However, after a long and tedious day attaching 250 databases, I now realise something... Each database is the same format, holding records for a day or week at a time, and I need to do the same thing to each one. Is there any way I can automate this to save myself a huge amount of time?

Examples of database names are:
xyz_201009062300
xyz_201009072300
xyz_201009081330

These are dates, but there is no set frequency to how often these files are created, so the names are inconsistant.

I need to export the data from 2 tables in each database, one called AlData and the other called ArcData to the same Excel spreadsheet...
Please someone help, I am totally out of my depth here!

Thanks!
Robert.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 12:15:25

Try this:

EXEC sp_msforeachdb 'if object_id(''..ArcData'') IS NULL RETURN;
IF OBJECT_ID(''tempdb..##ArcData'') IS NULL
SELECT * INTO ##ArcData FROM ..ArcData
ELSE
INSERT INTO ##ArcData SELECT * FROM ..ArcData

IF OBJECT_ID(''tempdb..##AlData'') IS NULL
SELECT * INTO ##AlData FROM ..AlData
ELSE
INSERT INTO ##AlData SELECT * FROM ..AlData'

SELECT * FROM ##AlData
SELECT * FROM ##ArcData

This will create 2 global temp tables and copy the data from all the relevant databases into them. The last 2 SELECT statements will return the collected data. As far as exporting them to Excel, that will depend on the method you use (SSIS, bcp to CSV file, something else?)

RobertStewart
Starting Member

7 Posts

Posted - 2011-05-19 : 04:13:06

Priceless.
Now I only have another 1000 Databases to add. Time to add another post.
Thanks very much for your help.
RS/

RobertStewart
Starting Member

7 Posts

Posted - 2011-05-19 : 05:07:31

Hmmmm... Sorry to reopen this, but can you tell me how to get it to write to a blank permanent database called MergedData?
Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 07:40:58

Replace all the references to the temp table:

EXEC sp_msforeachdb 'if ''?''=''MergedData'' OR object_id(''[?]..ArcData'') IS NULL RETURN;
INSERT INTO MergedData..ArcData SELECT * FROM [?]..ArcData
INSERT INTO MergedData..AlData SELECT * FROM [?]..AlData'

SELECT * FROM MergedData..AlData
SELECT * FROM MergedData..ArcData

And I fixed those pesky emoticons too.

RobertStewart
Starting Member

7 Posts

Posted - 2011-05-19 : 08:09:34

Thanks for the help Rob, but I am having trouble with this again :(
I have the script edited to now output all the data to MergedData, but it is not including all the files when I run the query, it is only doing, maybe 30 or 40, out of 310. Is there some sort of limit on this?
Thanks!

RobertStewart
Starting Member

7 Posts

Posted - 2011-05-19 : 08:33:08

Ahhh... Actually, I just needed to refresh the database. Consider this one closed bud!
Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources