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
 Help with running a series of sql queries at once

Author  Topic 

gravytrain
Starting Member

2 Posts

Posted - 2015-03-27 : 16:52:31

I have a large number of relatively simple sql queries that each perform tests on different tables returning different columns and rows for each test.

I would like to be able to run all of these at once and get the output in a usable format.

Would it be possible to run a single query or store procedure which could perform each test then output the results from each test to a new sheet in excel?

Could it output the results of each test to individual csvs?

Any suggestions or guidance would be welcomed.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-27 : 16:57:32
I would use an SSIS package for this. Use multiple Execute SQL Tasks that all run at the same time. They each would be connected to a data transformation task that uses an Excel destination.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gravytrain
Starting Member

2 Posts

Posted - 2015-03-30 : 03:30:48
Thanks for this I am exploring this option.

Can I also ask where would be the best place to store these individual bits of SQL?

I am not sure if they should be stored as functions, stored procedures or views? or some other method?

There are approx. 50 individual SQL tests - and there are occasions when it might be useful to call one or more on an ad hoc basis rather than all in one go.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-30 : 04:02:51
If you "store" them in a Stored Procedure you could have a parameter to the SProc that says which one(s) you want to execute.

Parameter can be a bit pattern, or a comma delimited list (or similar) and then do
[CODE]
CREATE PROCEDURE MySProc
@MyParameter int
AS
IF(@MyParameter & 1) = 1
BEGIN
.... do test number 1 ...
END
[/CODE]
or
[CODE]
CREATE PROCEDURE MySProc
@MyListParameter varchar(8000)
AS
-- Make sure that even first & last parameters are delimited
-- and remove any spaces
SELECT @MyListParameter = ','
+ REPLACE(@MyListParameter, ' ', '')
+ ','

IF @MyListParameter LIKE ',1,'
BEGIN
.... do test number 1 ...
END
[/CODE]
Go to Top of Page
   

- Advertisement -