Sql-server – BCP To Copy Headers

bcpsql serversql-server-2008-r2

I am using a stored procedure to populate a table with the data that I need. I am then using BCP to copy the data to a .csv file.

This process works, the one thing it is missing is that BCP is not copying the field names into row1 and I do not see a way to set Header=Yes.

How can this syntax be amended to add the header row?

declare @sql varchar(8000)
select @sql = 'bcp Test12.dbo.test out C:\testfolder\123.csv -c -t, -T -S '+ @@servername
exec master..xp_cmdshell @sql

Using the Stack Overflow Q & A provided by Serg, I updated my syntax to:

Select @sql = 'bcp "Select ''abc1'', ''abc2'', ''abc3'', ''abc4'', ''abc5''
UNION ALL Select [abc1], [abc2], [abc3], [abc4],[abc5] from test" queryout "'
Set @sql = @sql + 'C:\testfolder\123.csv' + '" -c -t, -T -S '+@@servername
exec master..xp_cmdshell @sql

However, this produces an error:

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]
Conversion failed when converting date and/or time from character string.

abc3 is a datetime column. Should I convert it differently? I did not have to provide any cast/convert when I used the initial syntax.

(I also tried to use CAST([abc3] As Date) but that threw the same error).

Best Answer

The problem with using UNION to include the column names is that column names are strings but the fields in the unioned query are not necessarily strings (as you discovered with that error posted in the Question). And you cannot convert names into other types such as INT, DECIMAL, MONEY, DATETIME, etc.

I did not have to provide any cast/convert when I used the initial syntax.

That is because BCP.exe was converting each non-string field of the table (or result set) into a string so that it could be placed into the output file. But when you UNION two or more queries together, you need to ensure that each position within each SELECT matches that same position in all the other SELECTs so that a result set can be built.

Hence you are left with a few options:

  1. Use UNION (similar to what you attempted), but then use CONVERT on the columns in the table that are not strings. For example (assuming that abc1, abc3, and abc4 are VARCHAR / NVARCHAR while acb2 is DATETIME, and abc5 is INT):

    UNION ALL SELECT [abc1], CONVERT(NVARCHAR(50), [abc2], 121), [abc3],
[abc4], CONVERT(NVARCHAR(200), [abc5]) FROM test

    The downside here is that you cannot use the simple syntax of just specifying the table name.

  2. Create a text file of the header row as a separate step, then execute BCP, then concatenate the two files together. I show two examples of doing that in the following DBA.StackExchange answer: Insert custom header row in BCP output

  3. Use .NET (SQLCLR or Console App; or PowerShell might even work) to execute the query and write the results to a text file. When using SqlCommand.ExecuteDataReaderto run the query, the returnedSqlDataReader` object will have the result set fields (names, datatypes, etc). You can use GetSchemaTable to get the result set field info.

    If this seems like a good option but you either can't code this or don't have the time and/or desire to, I created a SQLCLR Stored Procedure that does exactly this, called DB_BulkExport, and is included in the Full version (this is not available in the Free version) of SQL#. Sometime later this year there will be a stand-alone Console App version of this export, with additional features :-).

Related Question