T-SQL procedure - BCP - export CSV table with header-CodePudding

I have run the following query to export my SQL Server table as CSV. It is working fine. But now I want to add the column names as the first row. How is that possible?

DECLARE @archivoOUT varchar(800)
DECLARE @sql nvarchar(1000)

SET @archivoOUT = CONCAT('D:\archivosolicitudrestcate', FORMAT (GETDATE(), 'yyyyMMdd'),'.csv')

SET @sql = 'bcp "[dbo].[TEMP_res]" out ' @archivoOUT ' -S ' @@SERVERNAME ' -d CentroMedico -c -T -w'


EXEC master..xp_cmdshell @sql

CodePudding user response：

To add column names to your BCP out, you can change your syntax slightly.

You will need to select the columns that you want from the table instead of BCP'ing the entire table.

Currently you have

'bcp "[dbo].[TEMP_res]" out '

Modify the query syntax to select specific columns from the table

'bcp "select 'column1', 'column2' FROM [testdb].[dbo].[TEMP_res]" out'...

More details at Microsoft's learning site.

CodePudding user response：

I just create a view that does this:

SELECT 'Field_1, Field_2, Field_3'
union all
SELECT        Field_1,Field_2,Field_3
FROM            Table