Exporting SQL Query Results from SSMS to CSV

Declare @filename varchar(1000); --This will be main file Declare @tempDatafile varchar(100); --This will be used temp --Change this if your bcp.exe is at a different location Declare @exec_path varchar(200)=' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & '; Declare @bcp_cmd4 varchar(1000); Declare @query varchar(8000); Declare @columns varchar(8000);
--FileName with Path Select @filename = 'C:\users\testuser\Desktop\data.csv' Select @tempDataFile = 'C:\users\testuser\Desktop\temp.csv'
--These are the columns we are adding to our output file select @columns=' ''name'',''database_id'',''create_date'' '
--Creating File with Columns select @query='bcp.exe " select '+@columns+' " queryout '+@filename+' -c -t, -T -S' + @@SERVERNAME --Combining execution path and our bcp query Set @bcp_cmd4= @exec_path + @query --executing our query exec master..xp_cmdshell @bcp_cmd4
Set @query='bcp.exe "Select name,database_id,create_date from sys.databases (nolock)" queryout ' + @tempDatafile +' -c -t, -T -S'+@@SERVERNAME Set @bcp_cmd4= @exec_path + @query PRINT @bcp_cmd4; EXEC master..xp_cmdshell @bcp_cmd4;
/** Appending temp file at the end of header file **/ set @query= 'type '+@tempDatafile+' >> '+@filename+'' Set @bcp_cmd4= @exec_path + @query exec master..xp_cmdshell @bcp_cmd4
/** Delete temp file **/ set @query= 'del '+@tempDatafile+'' Set @bcp_cmd4= @exec_path + @query exec master..xp_cmdshell @bcp_cmd4

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sanchit Wadhwa

Sanchit Wadhwa

I help solve data problems |Need help ? email me > imsunchip@gmail.com | website > sanchitwadhwa.com | ❤ Books, music, dance, food, podcasts, soccer, writing