How to automatically save sql query results to .csv file

Question

I am trying to export my sql query results which I would like to do it automatically when you execute the query. I have seen examples of using output to which I tried to use but this would give me a syntax error as the examples did Select * From dbo.anyTable Output to but for me where I have a where and inner join in my query I have a ) a after it which is what I think is the problem. I know it is possible to save it by right clicking the mouse and save results as, but this is not what I want ideally as I would like to automate the whole system I am trying to do.

For example I would like to try and do this with part of my query:

Select top 1 a.NAME, COUNT(*) OVER() AS totalRows
From (Select de.NAME From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr 
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @filecontent = de.ENVIRONMENT_ID) a
OUTPUT TO @myPath
FORMAT TEXT
QUOTE '"'
WITH COLUMN NAMES;

EDIT I have the following query but it doesn't produce a file or any error's what am I doing wrong?

Set @OutputFilePath = 'C:\DeploymentPipelines'
Set @ExportSQL = 'EXEC master.dbo.xp_cmdshell ''bcp 
"Select top 1 a.NAME, COUNT(*) OVER() AS totalRows
From (Select de.NAME From dbo.DEPLOYMENT_ENVIRONMENT as de
Inner join dbo.DEPLOYMENT_RESULT as dr 
on dr.ENVIRONMENT_ID = de.ENVIRONMENT_ID
Where @filecontent = de.ENVIRONMENT_ID) a"
queryout "' + @OutputFilePath + '\results4.csv" -T -c -t -S DEV-BUILD01\SQLSERVER'''
Exec(@ExportSQL)

Answer 1

I've got some stored procs that do this for modifying .csv files, I found that using bcp was my best method for doing this in a TSQL script. The syntax is like this (taken from one of my live examples;

DECLARE @OutputFilePath nvarchar(max); SET @OutputFilePath = 'C:\Users\VirtualMachine1\Desktop\MasterFullOutput\Phase03'

DECLARE @ExportSQL nvarchar(max); SET @ExportSQL = N'EXEC master.dbo.xp_cmdshell ''bcp "SELECT TextData FROM DataConversionDB.dbo.DataScripts ORDER BY RowNumber" queryout "' + @OutputFilePath + '\OutputData.csv" -T -c -t -S WIN-SIITTJOB7OV'''

EXEC(@ExportSQL)

You will have to make sure that your SQL Server service login has access to the file path where you are outputting to (not your own permissions, the permissions of the SQL Server NT Service).

Here's some additional reading around the bcp command;

https://msdn.microsoft.com/en-GB/library/aa337544.aspx

https://www.simple-talk.com/sql/database-administration/working-with-the-bcp-command-line-utility/

Using bcp utility to export SQL queries to a text file

To narrow this down, give this example a go and see what happens;

DECLARE @ExportSQL nvarchar(max);

SET @ExportSQL = 'EXEC ..xp_cmdshell ''bcp "SELECT TOP 1 FROM sys.objects " queryout "C:\DeploymentPipelines\results4.csv" -T -c -t -S DEV-BUILD01\SQLSERVER'''

Exec(@ExportSQL)