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