Add Headers To BCP Export

Johnathan Simpson 586 Reputation points
2020-11-25T03:44:55.367+00:00

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.

Developer technologies Transact-SQL
{count} votes

Accepted answer
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2020-11-25T23:09:35.073+00:00

    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

    2 people found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,221 Reputation points
    2020-11-25T06:13:29.84+00:00

    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  
    

    42514-untitled.png

    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

    0 comments No comments

  2. Firoz 0 Reputation points
    2023-05-08T13:19:24.57+00:00
    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
    
    
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.