使用BCP查询输出获取列名

问题描述

我需要将表BCP到制表符分隔的文件中，但是我需要表的第一条记录中的列名.问题1:BCP没有为此设置开关，对吗?问题2:如果没有，为什么?

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 "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**

格式文件如下:

9.0
1
1 SQLCHAR 0 100 "\r\n" 1 Column_Names SQL_Latin1_General_CP1_CI_AS

这使我得到了一个列名文件，然后第二个BCP命令给了我一个数据文件，而我只是用DOS复制这两个文件.问题3:我聪明还是什么?问题4:为什么不起作用?我收到错误消息:

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:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when
copying into the Server

推荐答案

bcp不支持将列标题与数据一起导出，但是有一些解决方法，例如将标题导出到单独的文件中，然后合并标题和数据文件如下:

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:

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

这篇关于使用BCP查询输出获取列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！