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

Question

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

Answer 1

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