Automatically save output as Tab Delimited Textfile

_{Monday, June 17, 2019 1:14 PM}

Apologies if this is too simple - I am something of a noob at SQL! I'm using  SQL SM Studio 17

I have a query that runs in 3 parts

SELECT DISTINCT 
                           dbo.Report ...
FROM            ...

WHERE        (dbo.[Report1].[Company Group Name] = 'Green') 

GO

SELECT DISTINCT 
                           dbo.Report ...
FROM            ...

WHERE        (dbo.[Report2].[Company Group Name] = 'Green') 

GO

SELECT DISTINCT 
                           dbo.Report ...
FROM            ...

WHERE        (dbo.[Report3].[Company Group Name] = 'Green') 

What I want to do is have the outputs automatically save as  "Green ReportA.txt" , "Green ReportB.txt" and "Green ReportC.txt" . I have a list of about 50 different names that I need to run the reports for so it would save a lot of time if I didn't have to save the output from a grid in order to change the file names each time. If there were a way to cycle through a list to change the name that would also be great- any pointers gratefully received!

Many thanks

GaryB

_{Monday, June 17, 2019 1:37 PM ✅Answered | 1 vote}

I would suggest to create an SSIS package to loop the table names (Report1, Report2, …) and then export the outputs to the text files. You can set the "Company Group Name" as the input parameter so that you can change the value anytime when you want.

A Fan of SSIS, SSRS and SSAS

_{Monday, June 17, 2019 1:54 PM ✅Answered | 1 vote}

SSMS is a Management tool, not a ETL or reporting tool; for this you can use SSIS or SSRS instead.

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Tuesday, June 18, 2019 7:16 AM ✅Answered | 1 vote}

Hi GaryB 63,

>>What I want to do is have the outputs automatically save as  "Green ReportA.txt" , "Green ReportB.txt" and "Green ReportC.txt" . 

You can output the results to a file(.rpt file):

Right-click the query window.

Click Results to.

Click Results To File.

Run the query, and then select the location in which to save the results file.

If you open it by text file, it will show like the screenshoot:

Hope this could help you .

Best regards,

Dedmon Dai

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

_{Monday, June 17, 2019 2:46 PM | 1 vote}

Ok, I guess that explains why I couldn't find an answer. 

Thanks