locked
BCP RRS feed

  • Question

  • Hi,

    I want to have the header with the data while doing a builk copy

    I am using the beloscript

    EXEC master..xp_cmdShell  'bcp "select 'Col1','col2','col3','col4','col5' UNION ALL select top 10 * from DB1.dbo.tab1" queryout "C:\Result1.txt" /c /t, -T'

    is there some issue witnh this statement, this is not moving the data to the txt.

    Let me know the correct syntex, the above is what i find on the net.

    Thanks,

    Friday, January 16, 2015 10:01 AM

Answers

  • Could you try below.

    I replaced 'col1' by ''Col1'' and so on. Also instead of Top 10 *, provide column names and make sure your SELECT query runs fine

    EXEC master..xp_cmdshell 'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 * from DB1.dbo.tab1" queryout C:\Result1.txt -T -c'


    -Vaibhav Chaudhari

    • Proposed as answer by Eric__Zhang Sunday, January 25, 2015 4:15 AM
    • Marked as answer by Eric__Zhang Tuesday, January 27, 2015 9:19 AM
    Friday, January 16, 2015 10:38 AM
  • Hi Sudhakar,

    Your script is almost done except what Vaibhav mentioned. To make it work, you need to escape the single quotes by doubling them.

    USE DB1
    CREATE TABLE DB1.dbo.tab1(col1 VARCHAR(10),col2 VARCHAR(10),col3 VARCHAR(10),col4 VARCHAR(10),col5 VARCHAR(10))
    INSERT INTO DB1.dbo.tab1 SELECT 'A','B','C','D','E'
    
    EXEC master..xp_cmdShell  'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 * from DB1.dbo.tab1" queryout "C:\Result1.txt" /c /t, -T'
    DROP TABLE DB1.dbo.tab1


    By the way, if you will convert the txt file to csv file, best practice would be quote the column values with double quotes("") to avoid unexpected output when there're some commas within the values.

    EXEC master..xp_cmdShell  'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 QUOTENAME(col1,''\"''),QUOTENAME(col2,''\"''),QUOTENAME(col3,''\"''),QUOTENAME(col4,''\"''),QUOTENAME(col5,''\"'') from DB1.dbo.tab1 " queryout "C:\Result1.txt" /c /t, -T'
    

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Sunday, January 25, 2015 4:14 AM
    • Marked as answer by Eric__Zhang Tuesday, January 27, 2015 9:19 AM
    Monday, January 19, 2015 7:53 AM
  • Here is a working sample:

    create table tab1 (Col1 int, Col2  varchar(10), Col3  varchar(10), Col4  varchar(10), Col5  varchar(10))
    insert into Tab1 values(1,1,1,1,1)
    ,(1,1,1,1,3),(1,1,1,1,14),(1,1,1,1,4)
    
    select * from Tab1
    
    
    EXEC master..xp_cmdShell  'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 Cast(Col1 as varchar(10)),Col2,Col3,Col4,Col5 from testcs.dbo.tab1" queryout "C:\temp\Result1.txt" -c  -T  -t\t -S MCyourServer\MSSQL2014Instance';
    
    
    drop table Tab1

    1. double single quote; 

    2. list column out in your SELECT;

    3. align data type to string type to work with UNION ALL;

    4. Use a folder with right permission under C: root  like: c:\tem\ for file destination;

    5. Tab may be better to use instead of comma as separator;

    6. You may need server\instance name for trusted connection.


    • Edited by Jingyang Li Monday, January 19, 2015 4:12 PM
    • Proposed as answer by Eric__Zhang Sunday, January 25, 2015 4:14 AM
    • Marked as answer by Eric__Zhang Tuesday, January 27, 2015 9:19 AM
    Monday, January 19, 2015 3:40 PM

All replies

  • Could you try below.

    I replaced 'col1' by ''Col1'' and so on. Also instead of Top 10 *, provide column names and make sure your SELECT query runs fine

    EXEC master..xp_cmdshell 'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 * from DB1.dbo.tab1" queryout C:\Result1.txt -T -c'


    -Vaibhav Chaudhari

    • Proposed as answer by Eric__Zhang Sunday, January 25, 2015 4:15 AM
    • Marked as answer by Eric__Zhang Tuesday, January 27, 2015 9:19 AM
    Friday, January 16, 2015 10:38 AM
  • Refer the below link

    https://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

    for example,

    create table Question
    (id int, name varchar(10), someinfo numeric)
    
    insert into Question
    values (1, 'a', 123.12)
         , (2, 'b', 456.78)
         , (3, 'c', 901.12)
         , (4, 'd', 353.76)
    
    create view vw_bcptest
    as
      select 'col1' col1,'col2' col2,'col3' col3 union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from test.dbo.Question
    go
    declare @sql varchar(8000)
    select @sql = 'bcp master..vw_bcptest out f:\PowerSQL\test.txt -c -t, -T -S' + @@servername
    exec master..xp_cmdshell @sql

    --Prashanth

    Friday, January 16, 2015 3:37 PM
  • Hi Sudhakar,

    Your script is almost done except what Vaibhav mentioned. To make it work, you need to escape the single quotes by doubling them.

    USE DB1
    CREATE TABLE DB1.dbo.tab1(col1 VARCHAR(10),col2 VARCHAR(10),col3 VARCHAR(10),col4 VARCHAR(10),col5 VARCHAR(10))
    INSERT INTO DB1.dbo.tab1 SELECT 'A','B','C','D','E'
    
    EXEC master..xp_cmdShell  'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 * from DB1.dbo.tab1" queryout "C:\Result1.txt" /c /t, -T'
    DROP TABLE DB1.dbo.tab1


    By the way, if you will convert the txt file to csv file, best practice would be quote the column values with double quotes("") to avoid unexpected output when there're some commas within the values.

    EXEC master..xp_cmdShell  'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 QUOTENAME(col1,''\"''),QUOTENAME(col2,''\"''),QUOTENAME(col3,''\"''),QUOTENAME(col4,''\"''),QUOTENAME(col5,''\"'') from DB1.dbo.tab1 " queryout "C:\Result1.txt" /c /t, -T'
    

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Sunday, January 25, 2015 4:14 AM
    • Marked as answer by Eric__Zhang Tuesday, January 27, 2015 9:19 AM
    Monday, January 19, 2015 7:53 AM
  • Here is a working sample:

    create table tab1 (Col1 int, Col2  varchar(10), Col3  varchar(10), Col4  varchar(10), Col5  varchar(10))
    insert into Tab1 values(1,1,1,1,1)
    ,(1,1,1,1,3),(1,1,1,1,14),(1,1,1,1,4)
    
    select * from Tab1
    
    
    EXEC master..xp_cmdShell  'bcp "select ''Col1'',''col2'',''col3'',''col4'',''col5'' UNION ALL select top 10 Cast(Col1 as varchar(10)),Col2,Col3,Col4,Col5 from testcs.dbo.tab1" queryout "C:\temp\Result1.txt" -c  -T  -t\t -S MCyourServer\MSSQL2014Instance';
    
    
    drop table Tab1

    1. double single quote; 

    2. list column out in your SELECT;

    3. align data type to string type to work with UNION ALL;

    4. Use a folder with right permission under C: root  like: c:\tem\ for file destination;

    5. Tab may be better to use instead of comma as separator;

    6. You may need server\instance name for trusted connection.


    • Edited by Jingyang Li Monday, January 19, 2015 4:12 PM
    • Proposed as answer by Eric__Zhang Sunday, January 25, 2015 4:14 AM
    • Marked as answer by Eric__Zhang Tuesday, January 27, 2015 9:19 AM
    Monday, January 19, 2015 3:40 PM