SQL results to CSV file with column headers

Question

Hi

I am exporting SQL results into CSV file using BCP, I need to include column headers for the report. Any help is appreciated.

Below is my code

set @sql = 'bcp "select * from Sam.dbo.Samtmp" queryout '+@SAMLog_file_location+' -c -T -S' + convert(varchar,@@servername);
exec master..xp_cmdshell @sql

Answer 1

Please check the following threads for a solution to your issue. 

http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/8b840803-bdb0-46ab-a346-d3fcc2d85685/

http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/812b8eec-5b77-42a2-bd23-965558ece5b9/

---

Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

Answer 2

Hope this helps.

SELECT 'COLUMNNAME1' AS COLUMNNAME1, 'COLUMNNAME2' AS COLUMNNAME2 ...
UNION ALL
SELECT * FROM MYTABLE

http://www.sqlservercentral.com/Forums/Topic1036089-146-1.aspx

---

Thanks & Regards,
Prasanna Vaitheswaran

---

Please mark the post as 'Answer', if it addresses or resolves your query. Please click on 'Vote as Helpful', if it is helpful.

Answer 3

Hello Naveen,

This is a regular problem and you're not alone. The solution I found was to actually create a table to output first (you can always truncate it if you're wanting to use it dynamically). Into this table insert the column headers first, then append your data into it.

When you execute BCP it should output the column headers with it, unfortunately there's no easy way of being able to do this other than "tricking" SQL into performing the task for you.

Regards,

Johnny

Answer 4

SSIS can do this for you pretty easily.  Just mark the 'Column Names in the first data row' checkbox in the Flat File Destination.

Answer 5

Thanks Hima