Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to bring out column names with bcp?

    Hello, everyone:

    I used bcp to generate a txt file as:

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM Test..Orders" queryout "c:/test.txt " -U tester -P tester -c'

    It works fine except without column names of table. Does any one have idea that can bring out column names from table?

    Thanks

    ZYT

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Column names is not one of the arguments of bcp.

    You could do a "select * from <table> where 0=1" and save the output to a txt file, or script out the table schema and carry that across to the new location.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or you could use a union query with the column headings as literal strings in the row.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Or you could use a union query with the column headings as literal strings in the row.
    I was going to suggest that. The main problem is that you then need to convert all data to character.... which then means you need to be a little clever if you need to order your export for whatever reason.

    http://weblogs.sqlteam.com/brettk/ar...4/13/4395.aspx

    I thought Mladen on SQLTeam had blogged something more sophisticated but I can't find it.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by pootle flump
    I was going to suggest that. The main problem is that you then need to convert all data to character.... which then means you need to be a little clever if you need to order your export for whatever reason.

    http://weblogs.sqlteam.com/brettk/ar...4/13/4395.aspx

    I thought Mladen on SQLTeam had blogged something more sophisticated but I can't find it.

    I am honored
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - no offense. I mean I think he had some complicated automation code - I've never tried it. My own method, as it happens, is virtually identical to yours.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Never [played with it, but my guess is you could use com automation..good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    This the way I export my "headers" in specific situation but you can modify it so it works for you as well.

    DECLARE @tmp varchar(1000), @fld_list varchar(8000), @loop int, @i int, @select varchar(2000), @val_list varchar(8000)

    .................... YOUR CODE HERE .....................

    SET @i=(SELECT COUNT(*)
    FROM information_schema.columns
    WHERE TABLE_NAME='ut_RptQryProdTemp')

    SET @loop = 1
    SET @fld_list = ''
    SET @val_list = ''
    WHILE (@loop <= @i)
    BEGIN
    SELECT @select = 'SELECT ''['' + COLUMN_NAME + ' + '''],''' + ' AS F
    INTO ##fld_list
    FROM information_schema.columns
    WHERE TABLE_NAME=''ut_RptQryProdTemp'' AND ORDINAL_POSITION=' + convert(varchar(3),@loop)
    EXEC (@select)
    SELECT @tmp = (SELECT REPLACE(F, ',', '') + ' AS ' + REPLACE(F, 'PRODOUT],', '],') + '' FROM ##fld_list)
    SELECT @val_list = @val_list + @tmp
    SELECT @fld_list = @fld_list + REPLACE(STUFF(@tmp, 1, 0, 'CAST('), ' AS [', ' AS varchar) AS [')
    DROP TABLE ##fld_list
    SET @loop = @loop + 1
    END
    SET @fld_list = LEFT(@fld_list, LEN(@fld_list) - 1)
    SET @val_list = LEFT(@val_list, LEN(@val_list) - 1)
    SET @val_list = REPLACE(@val_list, ',[', ',''')
    SET @val_list = REPLACE(@val_list, '] AS', ''' AS')
    SET @val_list = REPLACE(@val_list, 'PRODOUT', '')
    SET @val_list = STUFF(@val_list, 1, 1, '''')

    EXEC ('SELECT ' + @val_list + ' UNION ALL SELECT ' + @fld_list + ' FROM ut_RptQryProdTemp ORDER BY _DEP, _FAM, _STYLE, _COLOR, [_SIZE]')
    As I said this is for a very specific situation, but if you're good with sql syntax you should be able to modify it.

    good luck!
    Last edited by ortho; 11-21-07 at 14:51.
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  9. #9
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Brett Kaiser
    Never [played with it, but my guess is you could use com automation..good luck
    Any docs on COM AUTOMATION please ?
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  10. #10
    Join Date
    Mar 2003
    Posts
    223
    Hello, everyone:

    Thanks for all replies.

    UNION cannot be used in bcp statement as SELECT part. I tried and got error. Pootle got a great idea. I did it and worked. Creat a view with all varchar column. Insert column names as first row. Then insert data from table. bcp to txt from view. This query will run automatically. The txt file name is changed by date. No problem on file name in bcp.

    ZYT

Posting Permissions

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