问题描述
我已经看到了许多尝试让 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
这篇关于使用 bcp 实用程序和 SQL Server 2008 将表导出到带有列标题(列名称)的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！