How to copy records from MS SQL Server table to a CSV file along with column names (column headers)

Question

I am using Bulk Copy Program utility (BCP) to export data from SQL server tables to CSV files; however, I do not see column names in the CSV files. Is there any option in BCP utility to copy the column names?

Answer 1

I do not know if there is any option in BCP utility. But I found a way to copy the columns. I used BCP command twice: first copy the columns to one file and then copy the records to another file. Finally combine those two files and delete the two files you created earlier. Check out the following example.

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + char(9), '') + column_name from SCHEMA_NAME.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='table_name'; select @colnames;" queryout folder_name\HeadersOnly.csv -c -U username -P password -S database_server_name

BCP SCHEMA_NAME.dbo.table_name out folder_name\TableDataWithoutHeaders.csv -c -U username -P password -S database_server_name

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

del HeadersOnly.csv

del TableDataWithoutHeaders.csv

You need to change the values highlighted in blue color. It will create a tab delimited CSV file.