Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: BCP - Output with Headings

    Does anyone know how to output the contents of a table via BCP utility to a file WITH the column headings?

    Thanks in advance,
    --Alla

  2. #2
    Join Date
    Dec 2002
    Location
    Sunny Florida
    Posts
    121
    Yes but it is a pain in the butt. Email me @ jfogel_34683@yahoo.com and I'll send you a sample of how it is done. Basically you create a table with the same column names as the table you want to export and you populate a single row in that table with the column names. Then you do a union or something in your BCP statement to get the headers to be the first row.

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    an easier way out is you can create a view, instead of a table, which will give you first row as column names and the other rows data from the table....and then bcp the view....

    so for eg for the Region table of northwind database i'll try out something like this

    SELECT *
    FROM (SELECT 'RegionId' AS Expr1, 'RegionDescription' AS Expr2
    UNION
    SELECT cast(RegionId AS char(4)), Regiondescription
    FROM Region) xyz
    ORDER BY Expr1 DESC

    and the resultant flat file will contain the following data

    RegionId RegionDescription
    4 Southern
    3 Northern
    2 Western
    1 Eastern

    Also you will have to cast all non compatible data types to char in your resultant table.....
    Last edited by nick.ncs; 06-14-07 at 02:52.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by nick.ncs
    an easier way out is you can create a view, instead of a table, which will give you first row as column names and the other rows data from the table....and then bcp the view....
    no need to create a view if you use this technique with the queryout option.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    no need to create a view if you use this technique with the queryout option.
    Does that include the headings?

    Was this fixed?:
    http://support.microsoft.com/kb/309555
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's a 4 year old bug, I assume it's fixed in 2005.

    I have never had any problems with queryout in 2005, and I have used this technique to export a csv with headigns with over 1b rows. big one!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •