0

I have run the following query to export my Ms SQL table as CSV. It working good. Now I want to add the field name as the first row. How is it possible?

declare @sql varchar(8000)
select @sql = 'bcp "select * from test_table" queryout C:\Test_SP\Tom.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

I know that I can specify the names @Red Devil answered. But the table is dynamic, Its fields are not fixed, It will change. I am trying to find a method to fetch the field names from the table definition and prepend it into the result CSV

Shijin TR
  • 7,516
  • 10
  • 55
  • 122
  • Does this answer your question? [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) – SMor Mar 20 '20 at 11:35
  • @SMor , No .because the table is dynamic . I dont know the field names – Shijin TR Mar 20 '20 at 11:39
  • The point is still the same - make an effort, do a little searching on the internet. This is not a free coding service. You have more than sufficient rep to know this. Need to know what columns exist in test_table? You look in the meta-data - sys.columns. – SMor Mar 20 '20 at 13:28
  • @SMor This is the first week I am dealing with MS SQL. I search lot for this but don't find a solution with bcp – Shijin TR Mar 20 '20 at 14:13
  • you should modify your title to include the need for dynamic columns – jamie Mar 20 '20 at 17:44

1 Answers1

0

Try this:

declare @sql varchar(8000)
select @sql = 'bcp "select 'col1', 'col2',... union all select * from test_table" queryout C:\Test_SP\Tom.csv -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
Red Devil
  • 2,343
  • 2
  • 21
  • 41