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!
16 thoughts on “Exporting from SQL Server to CSV with column names”
Thank you. This is exactly what I was looking for. Saved me hours of coding!
That script works miracles!
Is there a way to remove all empty columns?
thank you. this scenario was very very helpful & solved my issue
script is excellent but i need double quoted for each every value like (“sam”)
Could you give an example of the above script with actual values please?
SET @COLUMNS = ”
SET @Path = ‘\\servername\share\outputpath’
I am a little unclear, particularly on @COLUMNS what the actual input would look like.
I can see the confusion in the way I’ve written it – you don’t need to set this value at all. It needs to be set to ” when it’s created so that the query later can set the list of columns (if it’s still NULL, this won’t work).
You don’t need to change this value.
Wow! this worked for me. I appreciate it.
Great script!! I few changes and it worked.
I want to export the results of a stored procedure, i didn’t find where should I set my stored Procedure name
I am a little confused with the @@SERVERNAME variable, it is not defined in some place, from which I assume that it is taken from the system. However, I am no t sure what is the value it is taking in my case. On the other hand I have my query into a .csv file, When it is populated the temp table should I import this file into the variable YourSourceTable, or should I run this script immediately my SQL query script, with the source table stored in the memory?
The “@@SERVERNAME” is a system variable that holds the name of the current SQL Server – it’s in the script to ensure that the bcp command connects to the correct server. If you’re only running one instance on the server, connecting to LOCALHOST would work just fine, but it wouldn’t work if you’ve got multiple instances. In this case, if you’re running a named instance (like “DBSERVER\SQLINSTANCE7), it will connect correctly to the location you’re running the script.
It is possible to override the value returned by @@SERVERNAME to something else – there are a few reasons you’d want to do this, but the most common is when you physically rename the host and you want it to stay accurate. This means @@SERVERNAME may not actually be correct, but it’s the best way to get the database server’s name.
I have tried to execute this, but the Col returnrd is null? Could you pleae help me in this
SELECT @COLUMNS = @COLUMNS + c.name + ‘,’
from FIMU_POCTest.sys.syscolumns c
join FIMU_POCTest.sys.sysobjects t
on c.id = t.id
where t.name like ‘##OutputTable%’
order by colid
SELECT @COLUMNS as Cols INTO ##Cols
I’m not exactly sure what’s going on here, but my first guess is that you’re selecting from the table in your primary database (FIMU_POCTest) and the temp tables are always located in tempdb instead. Try to leave the original query pointing at tempdb and see if that resolves your problem.
Because of the way it’s appending columns into the variable, a query with no rows would result in a NULL variable.
Thank You, You helped my work!
Happy to have helped!