Add Headers To BCP Export

Question

I am using SQL Server 2016, and I am exporting data using BCP. I need the headers exported as well.

I saw an example where I could do
bcp "Select ''Field1'', ''Field2'', ''Field3'' UNION ALL Select CHAR(34) + Convert(varchar(10), field1, 101) + Char(34)

But my fields are not all varchar - some are dates, some are times etc so I do not think this option will work for me.

First is BCP still the best option for exporting a SQL Query to a .csv file?
Second how can I export the headers? (I am exporting about 30 columns...

Note - I am not selecting * from the table. The table contains columns that i do not want included in the csv file.

Answer 1

Look at my article about Bulk Load. I describe a better way to do this which does not involve UNION.
http://www.sommarskog.se/bulkload.html

Answer 2

CREATE FUNCTION fn_BCPTablesWithHeaders (@TableName varchar(100), @Directory varchar(500), @FileName varchar(100))
RETURNS 
varchar(max)
AS
BEGIN

	--DECLARE @TableName varchar(100) = 'SYM_Master'
	DECLARE @combinedString VARCHAR(MAX), @combinedString2 VARCHAR(MAX), @SQL varchar(max) 

SELECT 
	@combinedString = COALESCE(@combinedString + ', ', '')+''''+ LTrim(RTrim(c.name))+''''
FROM    
    tempdb.sys.columns c
INNER JOIN 
    tempdb.sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    tempdb.sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    tempdb.sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@TableName)

select @combinedString = 'Select '+@combinedString

SELECT 
	@combinedString2 = COALESCE(@combinedString2 + ', ', '') + 'convert(varchar(max),'+LTrim(RTrim(c.name))+')'
FROM    
    tempdb.sys.columns c
INNER JOIN 
    tempdb.sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    tempdb.sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    tempdb.sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@TableName)

select @combinedString2 = 'Select '+@combinedString2+' from '+@TableName

select @SQL = 'bcp "'+@combinedString+' union all '+@combinedString2+'" queryout '+@Directory+@FileName+' -c -t#~# -r\n -T'

Return @SQL
end




select @SQL = dbo.fn_BCPTablesWithHeaders('tempdb..##Datar', '\\<DIRECTORY STORE>','FileName.txt')
exec master..xp_cmdshell @SQL

Answer 3

Hi @Johnathan Simpson ,

Please refer below example about adding Headers To BCP Export.

Table prepare:

use [testdb]  
go  

drop table if exists [dbo].[SalesOrderDetailIn]  
  
CREATE TABLE [dbo].[SalesOrderDetailIn](  
[SalesOrderID] [int] NOT NULL,  
[CarrierTrackingNumber] [nvarchar](25) NULL,  
[ModifiedDate] [date] NOT NULL,  
[UpdateTime] [datetime] NOT NULL  
)  
GO  
  
insert into [dbo].[SalesOrderDetailIn] values  
(1,'a','2020-10-10','2020-10-10 09:23:34')  

BCP command:

bcp "select 'SalesOrderID', 'CarrierTrackingNumber','ModifiedDate','UpdateTime' union all SELECT convert(varchar(20),[SalesOrderID]),convert(varchar(20),[CarrierTrackingNumber]),CONVERT(nvarchar(30), [ModifiedDate], 120),CONVERT(nvarchar(30), [UpdateTime], 120) FROM [testdb].[dbo].[SalesOrderDetailIn]" queryout D:\People.txt -t, -c -T  

In addition, there are several methods to export a SQL Query to a .csv file.

1.Using the SQL Server export wizard from SSMS.
2. Using bcp Utility.
3. Using Import and Export Wizard from Visual Studio with SQL Server Data Tools (SSDT).

You could choose the most suitable one in your case.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table