Export table to file with column headers (column names) using the bcp utility and SQL Server 2008

This method automatically outputs column names with your row data using BCP.

The script writes one file for the column headers (read from INFORMATION_SCHEMA.COLUMNS table) then appends another file with the table data.

The final output is combined into TableData.csv which has the headers and row data. Just replace the environment variables at the top to specify the Server, Database and Table name.

set BCP_EXPORT_SERVER=put_my_server_name_here
set BCP_EXPORT_DB=put_my_db_name_here
set BCP_EXPORT_TABLE=put_my_table_name_here

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER%

BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER%

set BCP_EXPORT_SERVER=
set BCP_EXPORT_DB=
set BCP_EXPORT_TABLE=

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

Note that if you need to supply credentials, replace the -T option with -U my_username -P my_password

This method has the advantage of always having the column names in sync with the table by using INFORMATION_SCHEMA.COLUMNS. The downside is that it creates temporary files. Microsoft should really fix the bcp utility to support this.

This solution uses the SQL row concatenation trick from here combined with bcp ideas from here

For:

• Windows, 64 bit
• SQL Server (tested with SQL Server 2017 and it should work for all versions):

Option 1: Command Prompt

sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"

Where:

• [DATABASE].[dbo].[TABLENAME] is table to write.
• c:\dirname\file.csv is file to write to (surrounded in quotes to handle a path with spaces).
• Output .csv file includes headers.

Note: I tend to avoid bcp: it is legacy, it predates sqlcmd by a decade, and it never seems to work without causing a whole raft of headaches.

Option 2: Within SQL Script

-- Export table [DATABASE].[dbo].[TABLENAME] to .csv file c:\dirname\file.csv
exec master..xp_cmdshell 'sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"'

Troubleshoooting: must enable xp_cmdshell within MSSQL.

Sample Output

File: file.csv:

ID,Name,Height
1,Bob,192
2,Jane,184
3,Harry,186

Speed

As fast as theoretically possible: same speed as bcp, and many times faster than manually exporting from SSMS.

Parameter Explanation (optional - can ignore)

In sqlcmd:

• -s, puts a comma between each column.

• -W eliminates padding either side of the values.
• set nocount on eliminates a garbage line at the end of the query.

For findstr:

• All this does is remove the second line underline underneath the header, e.g. --- ----- ---- ---- ----- --.
• /v /c:"-" matches any line that starts with "-".
• /b returns all other lines.

Importing into other programs

In Excel:

• Can directly open the file in Excel.

In Python:

import pandas as pd
df_raw = pd.read_csv("c:\dirname\file.csv")

Some of the solutions here are overly complex. Here's one with 4 lines of code, no batch files, no external apps and all self-contained in the SQL server.

In this example, my table is named "MyTable" and it has two columns named Column1 and Column2. Column2 is an integer, so we need to CAST it to varchar for the export:

DECLARE @FileName varchar(100)
DECLARE @BCPCommand varchar(8000)
DECLARE @ColumnHeader varchar(8000)

    SET @FileName = 'C:\Temp\OutputFile.csv'
 SELECT @ColumnHeader = COALESCE(@ColumnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'
    SET @BCPCommand = 'bcp "SELECT '+ @ColumnHeader +' UNION ALL SELECT Column1, CAST(Column2 AS varchar(100)) AS Column2 FROM MyTable" queryout "' + @FileName + '" -c -t , -r \n  -S . -T'
   EXEC master..xp_cmdshell @BCPCommand

You could add this to a stored procedure to fully automate your .CSV file (with header row) creation.

I got a version based on what I saw previously. It helped me a lot to create export files as CSV or TXT. I'm storing a table into a ## Temp Table:

    IF OBJECT_ID('tempdb..##TmpExportFile') IS NOT NULL
DROP TABLE ##TmpExportFile;


DECLARE @columnHeader VARCHAR(8000)
DECLARE @raw_sql nvarchar(3000)

SELECT
              * INTO ##TmpExportFile
              ------ FROM THE TABLE RESULTS YOU WANT TO GET
              ------ COULD BE A TABLE OR A TEMP TABLE BASED ON INNER JOINS
              ------ ,ETC.

FROM TableName -- optional WHERE ....


DECLARE @table_name  VARCHAR(50) = '##TmpExportFile'
SELECT
              @columnHeader = COALESCE(@columnHeader + ',', '') + '''[' + c.name + ']''' + ' as ' + '' + c.name + ''
FROM tempdb.sys.columns c
INNER JOIN tempdb.sys.tables t
              ON c.object_id = t.object_id
WHERE t.NAME = @table_name

print @columnheader

                DECLARE @ColumnList VARCHAR(max)
SELECT
              @ColumnList = COALESCE(@ColumnList + ',', '') + 'CAST([' + c.name + '] AS CHAR(' + LTRIM(STR(max_length)) + '))'
FROM tempdb.sys.columns c
INNER JOIN tempdb.sys.tables t
              ON c.object_id = t.object_id
WHERE t.name = @table_name
print @ColumnList

--- CSV FORMAT                                       
SELECT
              @raw_sql = 'bcp "SELECT ' + @columnHeader + ' UNION all SELECT ' + @ColumnList + ' FROM ' + @table_name + ' " queryout \\networkdrive\networkfolder\datafile.csv -c -t, /S' + ' SQLserverName /T'
--PRINT @raw_sql
EXEC xp_cmdshell @raw_sql

  --- TXT FORMAT
       SET @raw_sql = 'bcp "SELECT ' + @columnHeader + ' UNION all SELECT ' + @ColumnList + ' FROM ' + @table_name + ' " queryout \\networkdrive\networkfolder\MISD\datafile.txt /c /S'+ ' SQLserverName /T'
       EXEC xp_cmdshell @raw_sql



DROP TABLE ##TmpExportFile

Please find below another way to make the same thing.

This procedure also takes in a schema name as a parameter in case you need it to access your table.

CREATE PROCEDURE Export_Data_NBA
@TableName nchar(50),
@TableSchema nvarchar(50) = ''

AS
DECLARE @TableToBeExported as nvarchar(50);

DECLARE @OUTPUT TABLE (col1 nvarchar(max));
DECLARE @colnamestable VARCHAR(max);
select @colnamestable = COALESCE(@colnamestable, '') +COLUMN_NAME+ ','
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
order BY ORDINAL_POSITION

SELECT @colnamestable = LEFT(@colnamestable,DATALENGTH(@colnamestable)-1)

INSERT INTO @OUTPUT
select @colnamestable

DECLARE @selectstatement VARCHAR(max);
select @selectstatement = COALESCE(@selectstatement, '') + 'Convert(nvarchar(100),'+COLUMN_NAME+')+'',''+'
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
order BY ORDINAL_POSITION

SELECT @selectstatement = LEFT(@selectstatement,DATALENGTH(@selectstatement)-1)

DECLARE @sqlstatment as nvarchar(max);

SET @TableToBeExported = @TableSchema+'.'+@TableToBeExported

SELECT @sqlstatment = N'Select '+@selectstatement+N' from '+@TableToBeExported

INSERT INTO @OUTPUT
exec sp_executesql @stmt = @sqlstatment

SELECT * from @OUTPUT

With a little PowerShell script:

sqlcmd -Q "set nocount on select top 0 * from [DB].[schema].[table]" -o c:\temp\header.txt
bcp [DB].[schema].[table] out c:\temp\query.txt -c -T -S BRIZA
Get-Content c:\temp\*.txt | Set-Content c:\temp\result.txt
Remove-Item c:\temp\header.txt
Remove-Item c:\temp\query.txt

Warning: The concatenation follows the .txt file name (in alphabetical order)

DECLARE @table_name varchar(max)='tableName'--which needs to be exported
DECLARE @fileName varchar(max)='file Name'--What would be file name

DECLARE @query varchar(8000)
DECLARE @columnHeader VARCHAR(max)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +''''  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

DECLARE @ColumnList VARCHAR(max)
SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +' AS VARCHAR)' +column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

DECLARE @tempRaw_sql nvarchar(max)
SELECT @tempRaw_sql = 'SELECT ' + @ColumnList + ' into ##temp11 FROM ' + @table_name 
PRINT @tempRaw_sql
EXECUTE sp_executesql  @tempRaw_sql


DECLARE @raw_sql nvarchar(max)
SELECT @raw_sql = 'SELECT  '+ @columnHeader +' UNION ALL SELECT * FROM ##temp11'
PRINT @raw_SQL
SET @query='bcp "'+@raw_SQL+'" queryout "C:\data\'+@fileName+'.txt" -T -c -t,'
EXEC xp_cmdshell @query


DROP TABLE ##temp11

Just used this for a DB Migration Activity. Helped a great bit - given that its a single line. I simply put this in the SQL Management Studio

SELECT 'sqlcmd -s, -W -Q "set nocount on; select * from [dbname].[dbo].['+ st.NAME + ']" | findstr /v /c:"-" /b >' + st.NAME + '.csv' + FROM sys.tables st

Copied the resultant set into a .bat file and I can now export the entire DB with each table into a CSV.