SQL Server T-SQL过程- BCP -导出带标题CSV表

de90aj5v  于 2022-12-10  发布在  其他
关注(0)|答案(4)|浏览(411)

我已经运行了以下查询,将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
fcipmucu

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,

'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.

rt4zxlrg

rt4zxlrg2#

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.

ddhy6vgd

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 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.

snvhrwxg

snvhrwxg4#

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"

相关问题