Help with running a series of sql queries at once

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/

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.

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources