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

2022-01-11 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

相关文章