How to output table or query to a flat file

Recently, I have a request to output a table/query to a flat file and gets Sftp to an external server

First step is to prepare the .csv file with column names.

It appears that we have two options here using xp_cmdshell

1. BCP : need to add header.csv to the contents.csv

Sample code below:

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from my_db_name.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='my_table_name'; select @colnames;" queryout HeadersOnly.csv -c -T -Smy_server_name

BCP my_db_name.dbo.my_table_name out TableDataWithoutHeaders.csv -c -t, -T -Smy_server_name

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

2. SQLcmd it will include headers and add space paddings . Also the file extension is limited compared to BCP

 

Good article to share

http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an