Save output of multiple queries into file?

Question

I have a SQL query that I have to run against multiple (15) tables in SQL Server Management Studio.

Is it possible to save the result of multiple queries into a file? (.txt, excel sheet?)

Using union is not possible because not all tables have an equal amount of columns.

The queries look somewhat like this

select *
from tableA
where main_id in (select id from maintable where date is null and status ='new')

select *
from tableB
where main_id in (select id from maintable where date is null and status ='new')

select *
from tableC
where main_id in (select id from maintable where date is null and status ='new')

select *
from tableD
where main_id in (select id from maintable where date is null and status ='new')

select *
from tableE
where main_id in (select id from maintable where date is null and status ='new')

Which dbms are you using? (ANSI SQL knows nothing about files.) — jarlh
– jarlh, Commented Nov 9, 2016 at 14:55
Possible duplicate of spool output to particular file from command prompt — OldProgrammer
– OldProgrammer, Commented Nov 9, 2016 at 14:57

Nirjhar Vermani · Accepted Answer · 2016-11-09 15:17:04Z

14

Try below:-

Open SQL Server Management Studio
Go to Tools > Options > Query Results > SQL Server > Results To Text
Then on right hand side, change output format to comma delimited.
Run your query and then right click on results and click save results to file.
Once done rename the file from .rpt to .csv

answered Nov 9, 2016 at 15:17

Nirjhar Vermani

1,2658 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Enrico Over a year ago

It doesn't really give a correct XML sheet (everything is in the same tab) But hey.. It's something. thanks :) +1

Tom Tom Over a year ago

I don't see this answer have solution for 'save multi table to one file'

Enrico · Accepted Answer · 2020-11-05 09:02:28Z

9

Go to the Query menu > "Results to"... and then pick "to file" or whichever you want. Change rpt extention to csv.

Be sure to re-run your queries.

edited Nov 5, 2020 at 9:02

Enrico

3,6352 gold badges38 silver badges48 bronze badges

answered Sep 17, 2018 at 12:49

CapnShanty

5791 gold badge8 silver badges22 bronze badges

2 Comments

Tom Tom Over a year ago

I don't see this answer have solution for 'save multi table to one file'

Ed Avis Over a year ago

It works, but even if you change the extension to CSV, you get a text file with columns separated by whitespace.

Venkataraman R · Accepted Answer · 2020-05-11 03:56:51Z

If you want multiple queries result to a single file, you can follow below steps:

Create view for every SQL query you have got. This is just for loading purpose.
You can use Import-Export wizard in SQL Server Management Studio. Right click database > Tasks > Export Data.
In the Wizard choose SQL Server database as source and Excel file as destination.
Choose export multiple tables and select views as the source, and in the target excel, a separate sheet will be mentioned as the destination in the excel file.
Go to next steps in the wizard and Finish the wizard
Now the views data will be loaded to separate sheets in the target excel
Now, you can remove the views, as you dont need them

all the above things can be done inside SSMS.

There are many other options to load data from Multiple ways to export data from SSMS into separate files

Collectives™ on Stack Overflow

Save output of multiple queries into file?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related