使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件

2021-12-09 00:00:00 csv header sql-server bcp

我已经看到了许多尝试让 bcp 实用程序将列名与数据一起导出的技巧.如果我所做的只是将表转储到文本文件中，那么让 bcp 添加列标题的最直接方法是什么?

I have seen a number of hacks to try to get the bcp utility to export column names along with the data. If all I am doing is dumping a table to a text file what is the most straightforward method to have bcp add the column headers?

这是我目前使用的 bcp 命令:

Here's the bcp command I am currently using:

bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T

推荐答案

最简单的方法是使用 queryout 选项并使用 union all 将列列表与实际表格内容

The easiest is to use the queryout option and use union all to link a column list with the actual table content

    bcp "select 'col1', 'col2',... union all select * from myschema.dbo.myTableout" queryout myTable.csv /SmyServer01 /c /t, -T

示例:

create table Question1355876
(id int, name varchar(10), someinfo numeric)

insert into Question1355876
values (1, 'a', 123.12)
     , (2, 'b', 456.78)
     , (3, 'c', 901.12)
     , (4, 'd', 353.76)

此查询将返回标题为第一行的信息(注意数值的转换):

This query will return the information with the headers as first row (note the casts of the numeric values):

select 'col1', 'col2', 'col3'
union all
select cast(id as varchar(10)), name, cast(someinfo as varchar(28))
from Question1355876

bcp 命令将是:

bcp "select 'col1', 'col2', 'col3' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876" queryout myTable.csv /SmyServer01 /c /t, -T

相关文章