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

Question

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

Gui LeFlea · Answer 1 · 2022-12-07T20:53:37.443

2

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 slightly. To select specific columns from the table try,

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

More details at Microsoft's learning site, but here is an even better answer directly from StackOverflow.

edited Dec 07 '22 at 20:53

answered Dec 07 '22 at 20:33

Gui LeFlea

795
3
12

1

This will only select the columns names (no table data) as written. – J.D. Dec 07 '22 at 20:39
You're right @J.D., good catch! It's been a while since I touched MSSQL, revised and updated. – Gui LeFlea Dec 07 '22 at 20:51
NP! Upvoted. Note you also have the same potential issue as the other answer, with the order of the results of the query not being ordered as expected (e.g. the column names not actually in the first row), without explicitly specifying the `ORDER BY` clause. – J.D. Dec 07 '22 at 21:00

score 1 · Answer 2 · answered Dec 08 '22 at 00:02

The method I always relied is the one referenced in the link @GuiLeFlea mentioned where you concatenate column and detail rows separately.

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

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

SET @sql = 'bcp "select ''column1'', ''column2'', ''column3''" queryout '+@archivoOUT+' -S '+@@SERVERNAME+' -d CentroMedico -c -T -w'
EXEC master..xp_cmdshell @sql 

SET @sql = 'bcp "[dbo].[TEMP_res]" out '+@archivoOUTdetails+' -S '+@@SERVERNAME+' -d CentroMedico -c -T -w'
EXEC master..xp_cmdshell @sql 

SET @sql = 'cmd /U /C type ' + @archivoOUTdetails + ' >> ' + @archivoOUT
EXEC master..xp_cmdshell @sql

The advantage is this will always order by correctly, regardless of execution plan.

J.D. · Answer 3 · 2022-12-08T14:44:03.183

So another way you can achieve your goal, guarantee the rows are ordered, and do it in a simple manner that only requires 1 call to xp_cmdshell is by adding a dummy sort ID column to the UNION ALL query, and then wrapping it in a CTE or subquery so you can order on it without having to select it:

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

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

SET @sql = 
CONCAT
(
    N'bcp ',
    N'"SELECT Column1, Column2, Column3 ',
    N'FROM ',
    N'( ',
    N'    SELECT ''Column1Name'' AS Column1, ''Column2Name'' AS Column2, ''Column3Name'' AS Column3, 1 AS SortId',
    N'    UNION ALL ',
    N'    SELECT Column1, Column2, Column3, 2 AS SortId ',
    N'    FROM dbo.TEMP_res ',
    N') AS Results ',
    N'ORDER BY SortId" ',
    N'out ', @archivoOUT, ' -S ', @@SERVERNAME, ' -d CentroMedico -c -T -w'
)

EXEC master..xp_cmdshell @sql

This is kind of the best of both worlds and then some, from the other answers.

Note I'm using CONCAT() so I can format the query in a human readable way, that's just my preference, it's not required. You can just stuff the whole code in a single line of code if you prefer, like your original BCP query string.

Nick Abbot · Answer 4 · 2022-12-08T16:54:48.683

-2

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

Edit: A UNION ALL does guarantee the datasets will be in order. The rows in each dataset may not be, but the order of the outputted datasets will be in the order they are executed. Just look at the Execution Plan, it always ends with "Concatenation - Append multiple input tables to form the output table"

edited Dec 08 '22 at 16:54

answered Dec 07 '22 at 20:35

Nick Abbot

310
3
7

This does not guarantee that the headers will be the first row when this view gets consumed by BCP. – J.D. Dec 07 '22 at 20:37
I think 'union ALL' pretty much guarantees it. – Nick Abbot Dec 07 '22 at 20:47
2

Nope, [without an `ORDER BY` clause](https://www.brentozar.com/archive/2020/04/why-ordering-isnt-guaranteed-without-an-order-by/) in the calling query against the view, it's not going to be deterministic / guaranteed. – J.D. Dec 07 '22 at 20:57
2

Order is NEVER ***guaranteed*** without an ORDER BY. In some circumstances a side effect will *often* / *usually* return the desired order. But can you imagine debugging THAT when it *sometimes* doesn't return the desired order? – MatBailie Dec 07 '22 at 22:38
No I'm not, rather I'm trying to be informative. The original link I provided is from Brent Ozar, who is a very experienced DBA. See also [this StackOverflow Post](https://stackoverflow.com/q/15766359/5059085) which also states order is not guaranteed without an `ORDER BY` clause, when using `UNION ALL`. In fact, the last comment in the top answer, by Nicolas NZ even states "*for the record, i've come here because my CTE wasn't preserving the order after multiple UNION ALL of selects*". So without an `ORDER BY` clause, it failed for him, and others as I've stated previously. – J.D. Dec 08 '22 at 13:53
Please see my answer on how to solve OP's problem with guaranteed ordering by using an `ORDER BY` clause. You can leverage the same methodology with your view. In fact, your implementation idea is just fine itself, but your answer should provide an example of using that View in a BCP string, with an `ORDER BY` clause, to guarantee the header rows are first, as OP asks for. – J.D. Dec 08 '22 at 13:55
@NickAbbot Definitely not when you've wrapped the `UNION ALL` in a view. Once you select from a view, any view, it doesn't matter what the query is beneath it, an `ORDER BY` clause needs to be supplied **in the query against the view** to guarantee order. That's literally the use case that Nicholas NZ had a problem with in the linked StackOverflow Post in my previous comment (except he wrapped the code with a CTE instead of a view, but same idea in this context). – J.D. Dec 08 '22 at 14:18
https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/union-all-transformation?view=sql-server-ver16 The transformation inputs are added to the transformation 'output one after the other; no reordering of rows occurs'. If the package requires a sorted output, ...blah, blah, blah. – Nick Abbot Dec 08 '22 at 15:12
You do realize you linked a document regarding [SSIS's](https://learn.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-ver16) Union All Transformation which is completely different than the [`UNION ALL` T-SQL operator](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver16) that your answer is using. So again, you referenced something irrelevant to this discussion. Sorry I can't immediately respond to every incorrect comment you make, I have better things to do lol. – J.D. Dec 08 '22 at 16:04

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

4 Answers4