2

I have automatically created a script with lots (15K) of statements.

How can I get the results to all be in the same results set, temp table or file?

I want to be able to look at the results easily:

select top 1 [utcDT] from [Mrix].[Pnl].[Specified_Prd_2_DS1M_1] order by [utcDT] desc;
...
select top 1 [utcDT] from [Mrix].[Pnl].[Specified_Prd_2_DS1M_2] order by [utcDT] desc;
  • Do all the scripts follow that same SELECT TOP 1 [utcDT] FROM [Table] ORDER BY [utcDT]? If they do you probably can just use a UNION. – Kirk Saunders Sep 26 at 13:53
  • Every statement is identical except the table name – ManInMoon Sep 26 at 14:00
  • Is there a reason you can't simply edit the generated file and search/replace select top 1 with insert into sometable select top 1? You could even generate the original file with the insert into sometable select top 1... – Scott Hodgin Sep 26 at 14:35
4

If you are going to keep all of these queries and run them, there are some options.


1

adding union all between all executions, you could use find and replace to add this to each select statement.

Example:

select name from sys.databases where name = 'master'
UNION ALL
select name from sys.databases where name = 'tempdb'

But when using order by you would have to change more parts of your query, an example:

 SET NOCOUNT ON;
 SELECT NAME FROM
 (SELECT TOP(1) name from sys.databases where name = 'master' order by name ) as [sysdatabases]
  UNION ALL 
SELECT NAME FROM
 (SELECT TOP(1) name from sys.columns  order by name )  as [syscolumns];

You cannot do this

SET NOCOUNT ON;
SELECT TOP(1) name from sys.databases where name = 'master' 
UNION ALL 
SELECT TOP(1) name from sys.columns 
ORDER BY NAME

Since it would sort on the entire result set.

Find and replace all in ssms

enter image description here


2

You could run result to file or text

Be sure to go to Tools - Options - Query Results - SQL Server - Results to Text and disable include column headers int he result set.

enter image description here

This is done so you don't get the same column header on each of these queries. You will have to add the headers afterwards.

Open a new query window to implement the changes, and select result to file.

enter image description here

Run the query with NOCOUNT

 SET NOCOUNT ON;

 SELECT name from sys.databases where name = 'master'

 SELECT name from sys.databases where name = 'tempdb'

Change the file type to csv if you'd like:

enter image description here

The result:

enter image description here


3

Insert all the values in a temp table & read from the temp table

 SET NOCOUNT ON;
 CREATE TABLE #temp(val varchar(255));

 INSERT INTO #temp(val)
 SELECT name from sys.databases where name = 'master';

 INSERT INTO #temp(val)
 SELECT name from sys.databases where name = 'tempdb';

 SELECT * FROM #temp;
 DROP TABLE #temp;

These are not the only options you have, and does not account for all the time it will take to execute all of these one by one.


4

Using Powershell

1) Save your query as a .sql file to your desired location

enter image description here

2) Run the next two statements with powershell while changing the parameters

import-module "sqlps" -DisableNameChecking

invoke-sqlcmd -Serverinstance instancename -inputFile "C:\temp\bla.sql" | out-File -filepath "C:\temp\TestOutput.txt"

3) validate the output

Output For this query

 SET NOCOUNT ON;
 SELECT name from sys.databases where name = 'master'
 SELECT TOP(1) name from sys.columns 

enter image description here

  • UNION ALL does not seem to work with my ORDER BY clause – ManInMoon Sep 26 at 14:08
  • @ManInMoon Right, sorry, added a part to the answer – Randi Vertongen Sep 26 at 14:12
  • Options 3 works for me - and is simplest . – ManInMoon Sep 26 at 14:20
  • @ManInMoon great, good to hear! I added a fourth one, with powershell :) – Randi Vertongen Sep 26 at 14:23
  • If you wrap the SELECTs in an sp_executesql call, you can then run it as part of an INSERT ... EXEC statement and get all the results into a table in one go: INSERT INTO #temp (val) EXECUTE sp_executesql N'SELECT TOP (1) name ...; SELECT TOP (1) name ...; ...';. When a stored procedure returns multiple result sets and you are inserting the results into a table, the effect is as though you were using UNION ALL to combine the results. (So it will fail if the various results are incompatible in terms of column number and data types.) – Andriy M Sep 29 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.