-1

I can't seem to find a way to retain the column headers using xp_cmdshell bcp to export a .csv or .txt file. Currently I'm using this code to export the tables to a txt file:

SET @sqlsave = 'master.sys.xp_cmdshell ''bcp "SELECT * FROM DDMVMS.dbo.'+@SelectedTable+'" queryout "'+@locationoutputtxt+'" -c -T -t; -S localhost\SQLEXPRESS'''

EXEC (@sqlsave);

This line works well, but the headers of the columns are not retained in the txt file. I can't find an easy way to retain them. I use this code in a loop, so adding them manually takes too much time.

Thank you!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Lore
  • 1
  • 1
  • Does this answer your question? [SQL BCP with column name](https://stackoverflow.com/questions/46347264/sql-bcp-with-column-name) – SMor Nov 14 '19 at 14:37
  • Even a trivial amount of searching would have found many, many suggestions for doing this. – SMor Nov 14 '19 at 14:38

1 Answers1

0

To do this, you should use query out (as you have) but your query needs to "union" together two queries. One query is your original query just as you've written, but first you must add an initial query to "select" your column headers. Just select your column names as string/char literals.

However, to do this you will likely run into data type issues if your original (and now 2nd query in the "union") query has data types/lengths other than string/char data types.

The initial query to get your column names:

select 'col_a', 'col_b', 'col_c'

will all set the data types for your entire query. so, all your columns in the 2nd query will need to be cast/converted into string/char compatible data types (you likely wont get away with "select *...". For instance, col_a will now be a char(5) or varchar(5) field. So, you just cast you data types and lengths accordingly.

So, you should wind up with something like:

select cast( 'col_a' varchar( 25 ) ), cast( 'col_b' as char( 10 ) ), cast( 'col_c' as varchar( 100 ) )
union
select cast( col_a as varchar( 25 ) ), cast( col_b as char( 10 ) ), cast( col_c as varchar( 100 ) )
  from blahblah
jamie
  • 745
  • 4
  • 11