Some SQL, some .NET, and whatever else


Exporting from SQL Server to CSV with column names

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
        @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
  INTO ##OutputTable
  FROM YourSourceTable

-- Generate a list of columns	
   from tempdb..syscolumns c
   join tempdb..sysobjects t
     on =
  where like '##OutputTable%'
  order by colid
-- 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!

Comments (10) Trackbacks (0)
  1. Thank you. This is exactly what I was looking for. Saved me hours of coding!

  2. That script works miracles!

    Is there a way to remove all empty columns?

  3. thank you. this scenario was very very helpful & solved my issue

  4. script is excellent but i need double quoted for each every value like (“sam”)

  5. Hi,

    Could you give an example of the above script with actual values please?

    Particularly for:
    SET @COLUMNS = ”
    SET @Path = ‘\\servername\share\outputpath’

    I am a little unclear, particularly on @COLUMNS what the actual input would look like.

    Many thanks,


    • 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.

  6. Wow! this worked for me. I appreciate it.

  7. Great script!! I few changes and it worked.

  8. 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.

Leave a comment

Why ask?

No trackbacks yet.