Export CSV file from SQL Server from script.

 This is a quick way to export a SQL  View or Table to a CSV file

The file can only be exported to a folder on the local SQL Server where it has run.

First all you data must be in one view or table. I use a Temp table to do the Job.

Once you have your data in one place:-

Declare @sql as varchar(8000). @outputpath  as varchar(3000)

set @outputpath  ='c:\temp\testcsv.csv'

select @sql = 'bcp "select  * from Yourdb..Temptable " queryout ' + @outputpath + ' -c -t, -T -S '  + @@servername 

exec master..xp_cmdshell @sql

 EXECUTE AS login = 'user'

and there you have it, the @sql needs to be a large Varchar for this to run or it fails. @otputpath need to be on  the local sql server

Export Select Query To Delimited File

The following code  will output the results of the query  (select * from  logic..temp1) to a CSV file

Please be aware that the output path will be to the sql machine if you use c:\.  Use \\servername\folder\filename.extention  to output the file to another machine.




declare @outputpath varchar(800) ----------Varchar(max) is not allowed
declare @sql varchar(8000)


set @outputpath = '\\SERVER\Shared_Drive\nick.csv'   --------- Set the Path


select @sql = 'bcp "select * from  logic..temp1"  queryout ' + @outputpath + ' -c -t, -T -S'  + @@servername

print @sql

exec master..xp_cmdshell @sql



the file will then appear in the appropriate folder.
To change the delimiter change the yellow Highlighted Character. for a pipe you will have to replace it with ^|  so the line looks like this ' -c -t^| -T -S'