none
How to get Data with column name in text file from temp table using BCP command in sql server

    Question

  • I can get data in txt file using bcp comand but no able, to get Data with column name in text file from temp table using BCP command in sql server

    Plz help me out, If any one know then post comment or mail me at rahul.agrawal@dbschenker.com

     

    • Changed type Naomi NModerator Thursday, January 05, 2012 1:40 PM Question rather than discussion
    Thursday, January 05, 2012 9:39 AM

Answers

  • That's correct that it is not working. That SQL seems to be some homebrewn syntax (or for some other product). What is that OUTPUT file doing there?

    You need something like this:

    Select rowno = 0, 'SKU' AS SKU ,'LOCATION' AS LOCATION AS LOCATION,
                 'SIZE' AS SIZE, 'COLOUR' AS COLOUR, 'UOM' AS UOM,
                 'AVAILABLEQTY' AS AVAILABLEQTY, 'INVENTORYDATE' AS INVENTORYDATE
    INTO     ##temp
    UNION ALL
    SELECT row_number() OVER (ORDER BY (SELECT 1)), SKU, LOCATION, SIZE,
                 COLOUR, UOM, ltrim(str(AVAILABLEQTY)),
                 convert(char(8), INVENTORYDATE, 112)
    FROM     ...

    Note here that I convert two the columns in the result set to string. This is necessary, or else you will get conversion errors when you run the query. You many need to do this for more columns, if their data type is not string.

    Then you specify this command for BCP like this:

    SELECT SKU, LOCATION, SIZE, COLOUR, UOM, AVAILABLEQTY, INVENTORYDATE FROM #temp ORDER BY rowno

    Note: all the above may contain trivial syntax errors, as I have no possibility to test. I hope that you are able to sort out the errors on your own.

    Good luck!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 05, 2012 12:02 PM

All replies

  • I think there is no way you can directly specify any option to include the column names with BCP. Workaround is to use union to get the column names

    bcp "select 'Colname' union all select col from <database>.<schema>.<object_name>" queryout <path> -c -S  -T


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, January 05, 2012 9:48 AM
  • I have run this command like

     

    Declare @Sql varchar(2000)

     

    Set @Sql='bcp "SELECT 'SKU','LOCATION','SIZE','COLOUR','UOM','AVAILABLEQTY','InventoryDate'

    union all select SKU,LOCATION,SIZE,COLOUR,UOM,AVAILABLEQTY,InventoryDate from webwms201211..StockBalance#" queryout \\10.213.173.187\chicco\Outbound\ArtSana\Inv_Report'

    +'_'+'REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103)'+'.txt -c -T -S10.213.173.187 '+'-Usa -Psql2000'

    PRINT @Sql 
     EXEC  MASTER..XP_CMDSHELL @Sql

    give an ERROR

    Msg 170, Level 15, State 1, Line 2

    Line 2: Incorrect syntax near 'SKU'.

     

     

    But in this if i execute only

    SELECT 'SKU','LOCATION','SIZE','COLOUR','UOM','AVAILABLEQTY','InventoryDate'

     

     

    union all select SKU,LOCATION,SIZE,COLOUR,UOM,AVAILABLEQTY,InventoryDate from webwms201211..StockBalance#

    it work fine...


    Thursday, January 05, 2012 10:01 AM
  • use the set @sql like below:

    Set @Sql='bcp "SELECT ''SKU'',''LOCATION'',''SIZE'',''COLOUR'',''UOM'',''AVAILABLEQTY'',''InventoryDate''

    union all select SKU,LOCATION,SIZE,COLOUR,UOM,AVAILABLEQTY,InventoryDate from webwms201211..StockBalance#" queryout \\10.213.173.187\chicco\Outbound\ArtSana\Inv_Report'

    +'_'+'REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103)'+'.txt -c -T -S10.213.173.187 '+'-Usa -Psql2000'


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, January 05, 2012 10:13 AM
  • Its runnning but not creating txt file at destination....

    giving msg.

    usage: bcp {dbtable | query} {in | out | queryout | format} datafile
      [-m maxerrors]            [-f formatfile]          [-e errfile]
      [-F firstrow]             [-L lastrow]             [-b batchsize]
      [-n native type]          [-c character type]      [-w wide character type]
      [-N keep non-text native] [-V file format version] [-q quoted identifier]
      [-C code page specifier]  [-t field terminator]    [-r row terminator]
      [-i inputfile]            [-o outfile]             [-a packetsize]
      [-S server name]          [-U username]            [-P password]
      [-T trusted connection]   [-v version]             [-R regional enable]
      [-k keep null values]     [-E keep identity values]
      [-h "load hints"]         [-x generate xml format file]
    NULL

    Thursday, January 05, 2012 10:26 AM
  • Is all this, can i do with

    Select

    'SKU','LOCATION','SIZE','COLOUR','UOM','AVAILABLEQTY',

    'INVENTORYDATE'

     

    UNION

     

    SELECT * FROM webwms201211..StockBalance# INTO OUTFILE "E:\RahulAgrawal\Inv_Rep.txt"

    FIELDS TERMINATED

    BY ',' OPTIONALLY ENCLOSED BY '"'

     

    ORDER BY SKU

    but not working Error Msg.

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'INTO'.

    Thursday, January 05, 2012 11:39 AM
  • Have a look:

    http://social.msdn.microsoft.com/Forums/en/transactsql/thread/5c189d19-b9ef-4e01-a448-47fff2f7c836


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Thursday, January 05, 2012 11:39 AM
  • That's correct that it is not working. That SQL seems to be some homebrewn syntax (or for some other product). What is that OUTPUT file doing there?

    You need something like this:

    Select rowno = 0, 'SKU' AS SKU ,'LOCATION' AS LOCATION AS LOCATION,
                 'SIZE' AS SIZE, 'COLOUR' AS COLOUR, 'UOM' AS UOM,
                 'AVAILABLEQTY' AS AVAILABLEQTY, 'INVENTORYDATE' AS INVENTORYDATE
    INTO     ##temp
    UNION ALL
    SELECT row_number() OVER (ORDER BY (SELECT 1)), SKU, LOCATION, SIZE,
                 COLOUR, UOM, ltrim(str(AVAILABLEQTY)),
                 convert(char(8), INVENTORYDATE, 112)
    FROM     ...

    Note here that I convert two the columns in the result set to string. This is necessary, or else you will get conversion errors when you run the query. You many need to do this for more columns, if their data type is not string.

    Then you specify this command for BCP like this:

    SELECT SKU, LOCATION, SIZE, COLOUR, UOM, AVAILABLEQTY, INVENTORYDATE FROM #temp ORDER BY rowno

    Note: all the above may contain trivial syntax errors, as I have no possibility to test. I hope that you are able to sort out the errors on your own.

    Good luck!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 05, 2012 12:02 PM