SQL BCP with column name

Question

I am trying to export my stored procedure to a .csv file using BCP. It does give me a output file in .CSV but it does not print column name. Below is the script. Please look at and let me know what i am missing

    DECLARE @command VARCHAR(4000)
    declare @fulldate varchar(30) = convert(varchar,GETDATE(),112)
    declare @year varchar(30) = left(@fulldate,4)
    declare @day  varchar(30) = right(@fulldate,2)
    declare @month varchar(30) = left(right(@fulldate,4),2)
    DECLARE @FileDirectory VARCHAR(1000) = 'c:\'
    DECLARE @FileName VARCHAR(255)= 'TestingDOC' + @month + '.' + @day + '.'      + @year  + '.txt'
    declare @attach varchar(1255) = @fileDirectory + @fileName

    SET @command = 'bcp "select * from ngprod.dbo.TEMP_PAS"'
        + ' queryout "' + @FileDirectory + @FileName + '"'
        + ' -c -t, -T -S'+ @@servername
    EXEC master..xp_cmdshell @command

While easy to write in this case, SQL is not really the best tool for ETL, especially as things start getting complicated. I would suggest moving to an ETL tool. — Raj More, Sep 21 '17 at 15:17
Could you please provide me an example of how to use power shell script in stored procedure to output a file with header — Paul I Stone, Sep 21 '17 at 15:27
Possible duplicate of [export table to file with column headers (column names) using the bcp utility and SQL Server 2008](https://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an) — TT., Sep 21 '17 at 16:04
Also, this one: [Getting column names with BCP queryout](https://stackoverflow.com/a/24733192/243373) — TT., Sep 21 '17 at 16:05

score 1 · Answer 1 · answered Sep 21 '17 at 15:04

1

The easiest thing to do is do a union all with the column names when you are using queryout.

so an example is provided below.

select 'col1','col2',col3','col4' ... etc
union all
select col1,col2,col3,col4 ... etc from yourtable

answered Sep 21 '17 at 15:04

ttallierchio

460
7
17

score 0 · Accepted Answer · edited Sep 25 '17 at 22:43

After a lot of trail and error below is the answer on how to add column

First create a header.txt file (inside the header file add all of you header) for example if the header file need firstname, lastname etc

second paste the below query in your stored procedure

DECLARE @command VARCHAR(4000)

DECLARE @FileDirectory VARCHAR(1000) = 'c:\test\'

DECLARE @HeaderFile varchar(255) = 'Headers.txt'

DECLARE @FileName VARCHAR(255)

SET @FileName = 'TestFile_' + CONVERT(VARCHAR,GETDATE(),112)

SET @command = 'bcp "select * from TESTDB.dbo.TEST_Table"'

       + ' queryout "' + @FileDirectory + @FileName + '.txt"'

       + ' -c -q -t, -T  -S'+@@servername

EXEC master..xp_cmdshell @command

SET @command =  'copy "' + @FileDirectory + @HeaderFile + '"+"' + @FileDirectory + @FileName + '.txt"' +  ' "' + @FileDirectory + @filename + '.csv"'

EXEC master..xp_cmdshell @command

SET @command = 'del "' + @FileDirectory + @FileName + '.txt"'

EXEC master..xp_cmdshell @command

SQL BCP with column name

2 Answers2

Linked