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

by

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

More Related Contents:

  1. Turning a Comma Separated string into individual rows
  2. How to split a comma-separated value to columns
  3. How to export data as CSV format from SQL Server using sqlcmd?
  4. Import Multiple CSV Files to SQL Server from a Folder
  5. Bulk Insert Partially Quoted CSV File in SQL Server
  6. SQL Server Bulk insert of CSV file with inconsistent quotes
  7. How to export data from SQL Server 2005 to MySQL [closed]
  8. SQL Server BCP export corrupted file?
  9. How can I import an Excel file into SQL Server? [closed]
  10. SQL Server – join rows into comma separated list
  11. SQL Server – error when creating stored procedure
  12. SQL Server insert if not exists best practice [closed]
  13. Are GUID collisions possible?
  14. best way to convert and validate a date string
  15. Why do table names in SQL Server start with “dbo”?
  16. Add alternating row color to SQL Server Reporting services report
  17. LIKE vs CONTAINS on SQL Server
  18. How do I enable MSDTC on SQL Server?
  19. Errors in SQL Server while importing CSV file despite varchar(MAX) being used for each column
  20. SQL Server, the misleading XLOCK & optimizations
  21. migrate data from MS SQL to PostgreSQL?
  22. “Prevent saving changes that require the table to be re-created” negative effects
  23. How to change column datatype in SQL Server database without losing data?
  24. What are the differences between Merge Join and Lookup transformations in SSIS?
  25. Convert UTC Milliseconds to DATETIME in SQL server
  26. freeTDS not using its config
  27. @@ERROR and/or TRY – CATCH
  28. replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)
  29. How to make a passthrough / passthru query editable?
  30. Tool for Scripting Table Data

Leave a Comment