How to export to file with bcp with header

Question

Hi i have query working file to export to .txt file , i just want to add header , any help would be much appriciated , the column name is much appriciated

declare @sql varchar(8000) select @sql = 'bcp "select top 50 * from [Alltrigger].[dbo].[customlook]" queryout \live-ftp\sftp_upload\allfile\saad_test.txt -c -t^| -T -S' + @@servername exec master..xp_cmdshell @sql --- header file exec master..xp_cmdshell 'BCP "select
'provided column name' " queryout \prod-ftp\sftp_upload\allstate\header.csv -c T -t'

Please reply , i have tried to make header file seprate but its not working

Answer 1

you'll need to use queryout option for bcp, and build your column list via string literals. You can get rid of the dynamically generated SQL too.

bcp "select '[guid]', 'uploadDate', 'fileName', 'email', 'profileTypeID', 'firstName', 'lastName', 'prospectID', 'prospe‌​ctingPermission', 'relationshipPermission', 'advicePermission', 'transactionalPermission', 'p‌​olicy', 'postalCode', 'language', 'phone', 'businessPhone', 'cell', 'country', 'province', 'city', 'expiryDate', 't‌​ransactionDate', 'source', 'consentBy', 'agentNumber', 'aia', 'suppression' union all select * from [Alltrigger].[dbo].[custom]" queryout \\prod-ftp\sftp_upload\allstate\mark_test.txt -c -t^| -T -t