How to export data with header from sql server table to any flat file

[saket]

Hi,

I am trying to export the data with header from sql server table using BCP and data is coming without header .
when i used union all then data conversion issue is coming and i don,t want to convert every column as char. because manually its very difficult to convert for every table.

Please suggest.

Thanks in advance.

[Kristen]

Two choices (I think!):

1. Use UNION ALL to output, first, a list of column names followed by the results of the actual query. All columns in the actual query will need to be CAST to String (There is code on the Internet that will simplify the task ,,, but ...)

2. Output the Column Names to one file (possibly by querying the System Tables to get the current column names), and the Data to a second file, and then concatenate the two files together (and delete the working-files)

Both are a PITA ...

Dunno anything about SSIS - maybe that provides a better route.

[jeffw8713]

@Kristen

Two choices (I think!):

Use UNION ALL to output, first, a list of column names followed by the results of the actual query. All columns in the actual query will need to be CAST to String (There is code on the Internet that will simplify the task ,,, but ...)
Output the Column Names to one file (possibly by querying the System Tables to get the current column names), and the Data to a second file, and then concatenate the two files together (and delete the working-files)

Both are a PITA ...

Dunno anything about SSIS - maybe that provides a better route.

SSIS would be the route to go - you can define the output file in many different formats with or without column headers depending on the format. If you do not want to use SSIS then you can use Powershell with Invoke-SqlCmd to output to an object - then pass that one of the export cmdlets.