SQL 使用 BCP queryout 获取列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23226847/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting column names with BCP queryout
提问by Dom
I need to BCP a table into a tab-delimited file, but I need the column names in the first record of the table. Question 1: Am I right that BCP does not have a switch for this? Question 2: If not, why?
我需要将表 BCP 转换为制表符分隔的文件，但我需要表的第一条记录中的列名。问题 1：我说 BCP 没有为此提供开关是对的吗？问题2：如果不是，为什么？
I tried to do the following:
我尝试执行以下操作：
BCP "declare @colnames varchar(max); select @colnames=coalesce (@colnames+char(9), '')
+ Column_Name from db.information_Schema.columns where table_name='table1' order by
ordinal_position; select @colnames" queryout Table1_Columns.tsv -S?? -U?? -P?? -f** -e**
The format file looks like this:
格式文件如下所示：
9.0
1
1 SQLCHAR 0 100 "\r\n" 1 Column_Names SQL_Latin1_General_CP1_CI_AS
This gets me a file of the column names, then a second BCP command gets me a file of data, and I just DOS-copy the two together. Question 3: Am I clever or what? Question 4: Why doesn't it work? I get the error:
这为我提供了一个列名文件，然后第二个 BCP 命令为我提供了一个数据文件，我只是将这两个文件复制到一起。问题3：我聪明还是什么？问题 4：为什么它不起作用？我收到错误：
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when
copying into the Server
回答by Mona Yehia
bcp does not support exporting the column headers with the data, however there are some workarounds like exporting the headers in a separate file, then merging both the headers and data files as the following:
bcp 不支持将列标题与数据一起导出，但是有一些解决方法，例如将标题导出到单独的文件中，然后将标题和数据文件合并如下：
exec master..xp_cmdshell 'BCP "select 'SETTINGS_ID','GROUP_NAME'" queryout d:\header.csv -c -T -t,'
exec master..xp_cmdshell 'BCP "select SETTINGS_ID,GROUP_NAME from [DB]..[TABLE]" queryout "d:\columns.csv" -c -t, -T '
exec master..xp_cmdshell 'copy /b "d:\header.csv"+"d:\columns.csv" "d:/result.csv"'
You may also delete the unused files:
您也可以删除未使用的文件：
exec master..xp_cmdshell 'del "d:\header.csv"'
exec master..xp_cmdshell 'del "d:\columns.csv"'
Or maybe you can combine all the data in a view (adding headers) and export it
或者，您可以将所有数据合并到一个视图中（添加标题）并导出
