asked on

BCP include column names

We are using BCP to query out from a table into a .csv file to another server. Is there a way to get it to pick up the column names with the query, or some type of -switch we should be using.

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

twoboats

I don't believe you can get the column names out.

However, you could use a linked server to the csv file (excel type) and do a select insert. Your .csv sheet could have column names that way.

bcp /? gives all the switches

SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

twoboats

Actually, just thought of it.

If you bcp out of a view like this#

Create mybcp view as

select 'col1name',col2name', 'col3name'...
union
select cast(col1 as varchar), (col2 as varchar), (col3 as varchar)....

you'll get the literal strings on your output file

twoboats

if the order give you a problem (i.e. the strings appear in the middle of the results...

select c1,c2,c3
from
(select 1 i,'col1name' c1,col2name' c2, 'col3name' c3...
union
select 2 i,cast(col1 as varchar) c1, (col2 as varchar) c2, (col3 as varchar) c3...) t
order by t.1 asc

twoboats

the second select has casts missing....

select t.c1,t.c2,t.c3
from
(select 1 i, 'col1name' c1, col2name' c2, 'col3name' c3...
union
select 2 i,cast(col1 as varchar) c1, cast(col2 as varchar) c2, cast(col3 as varchar) c3...) t
order by t.i asc

ozkhillscovington

ASKER

Twoboats-- will that work with a global temp table? I am trying to adapt your code to fit my table, but having issues. My columns are:

Market, OrderType, AccountType, Phonenumber - in temp table ##wkcancel

ozkhillscovington

ASKER

I found this link, which works if I drop into perm table. If I try this with temp table, it returns nulls.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=150595&SiteID=1

ASKER CERTIFIED SOLUTION

twoboats

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ozkhillscovington

ASKER

I've actually decided to use DTS, since it copies the headers. The link I noted before did work okay, but some of these reports have 45 columns and I really didn't want to tie myself to alot of coding in case the table structure changed.

But thanks for all your help. I'm awarding twoboats the points on this one for his diligent suggestions!!

ozkhillscovington

ASKER

Sorry twoboats, I decided to do a split since everyone helped out! Thanks again.. EVERYONE!!

twoboats

No problem.

Anvie

Thank you to all, I use union instead. I like bcp because it run fast and I think it is more efferent. ;-)

Once again thank you