SQL Server can easily export to CSV file, but it exports just the data, without the column names included. In order to export the column names, you need to actually perform two exports – one with the column names, and one with the data – and then combine the two files into a single file. It populates
You could do this using any query you want – native SQL, a linked server, a stored procedure, or anything else – and the results will export the same way once they’re in the temp table. Since it builds the list of column name dynamically as well, you only need to change out the query being executed and set the export location – no other configuration is necessary.
-- Declare the variables DECLARE @CMD VARCHAR(4000), @DelCMD VARCHAR(4000), @HEADERCMD VARCHAR(4000), @Combine VARCHAR(4000), @Path VARCHAR(4000), @COLUMNS VARCHAR(4000) -- Set values as appropriate SET @COLUMNS = '' SET @Path = '\\servername\share\outputpath' -- Set up the external commands and queries we'll use through xp_cmdshell -- Note that they won't execute until we populate the temp tables they refer to SET @CMD = 'bcp "select * from ##OutputTable" queryout "' + @Path + '\Temp_RawData.csv" -S ' + @@SERVERNAME + ' -T -t , -c' SET @HEADERCMD = 'bcp "SELECT * from ##cols" queryout "' + @Path + '\Temp_Headers.csv" -S ' + @@SERVERNAME + ' -T -t , -c' SET @Combine = 'copy "' + @Path + '\Temp_Headers.csv" + "' + @Path + '\Temp_RawData.csv" "' + @Path + '\MyCombinedFile.csv"' SET @DelCMD = 'del "' + @Path + '\Temp_*.csv"' -- Create and populate our temp table with the query results SELECT * INTO ##OutputTable FROM YourSourceTable -- Generate a list of columns SELECT @COLUMNS = @COLUMNS + c.name + ',' from tempdb..syscolumns c join tempdb..sysobjects t on c.id = t.id where t.name like '##OutputTable%' order by colid SELECT @COLUMNS as Cols INTO ##Cols -- Run the two export queries - first for the header, then for the data exec xp_cmdshell @HEADERCMD exec xp_cmdshell @CMD -- Combine the two files into a single file exec xp_cmdshell @Combine -- Clean up the two temp files we created exec xp_cmdshell @DelCMD -- Clean up our temp tables drop table ##cols drop table ##OutputTable
If you have any suggestions or run into any issues, please let me know!