How can I write results to SAME results set? 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;
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





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.



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.



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:



The result:





3

Insert all the values in a temp table &amp; 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



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