我已经运行了以下查询,将SQL Server表导出为CSV格式。运行正常。但现在我想将列名添加为第一行。这怎么可能呢?
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
4条答案
按热度按时间fcipmucu1#
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,
Modify the query syntax slightly. To select specific columns from the table try,
More details at Microsoft's learning site, but here is an even better answer directly from StackOverflow.
rt4zxlrg2#
The method I always relied is the one referenced in the link @GuiLeFlea mentioned where you concatenate column and detail rows separately.
The advantage is this will always order by correctly, regardless of execution plan.
ddhy6vgd3#
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 theUNION ALL
query, and then wrapping it in a CTE or subquery so you can order on it without having to select it: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.snvhrwxg4#
I just create a view that does this:
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"