Export table to file with column headers (column names) using the bcp utility and SQL Server 2008
bcp, csv, header, sql-server
Solution
The easiest is to use the `queryout` option and use `union all` to link a column list with the actual table content
bcp "select 'col1', 'col2',... union all select * from myschema.dbo.myTableout" queryout myTable.csv /SmyServer01 /c /t, -T
An example:
create table Question1355876
(id int, name varchar(10), someinfo numeric)
insert into Question1355876
values (1, 'a', 123.12)
, (2, 'b', 456.78)
, (3, 'c', 901.12)
, (4, 'd', 353.76)
This query will return the information with the headers as first row (note the casts of the numeric values):
select 'col1', 'col2', 'col3'
union all
select cast(id as varchar(10)), name, cast(someinfo as varchar(28))
from Question1355876
The bcp command will be:
bcp "select 'col1', 'col2', 'col3' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876" queryout myTable.csv /SmyServer01 /c /t, -T
Problem
I have seen a number of hacks to try to get the bcp utility to export column names along with the data. If all I am doing is dumping a table to a text file what is the most straightforward method to have bcp add the column headers? Here's the bcp command I am currently using: ``` bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T ```