bcp查询SQL Server的自定义列名称 [英] Custom column name for bcp queryout SQL Server

问题描述

我想重命名bcp queryout命令的select子句中的列名.我尝试了以下变体，但它们均无效.我想将第一和第三列分别重命名为email_address和id.

我在批处理脚本中调用bcp命令.

bcp "select email as 'email_address', first_name, p_id  as 'id' from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email as [email_address], first_name, p_id  as [id] from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email 'email_address', first_name, p_id 'id' from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email email_address, first_name, p_id id from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,

有人可以指出我的正确解决方案吗?

解决方案

如果查看bcp生成的文本文件，您会注意到列名没有导出.仅导出数据.

Alex发表的链接描述了一种在输出中添加列名称的方法.实际上，您是使用UNION将字段作为第一个数据列添加到数据中的.我建议不要这样做，因为它要求将所有字段都强制转换为字符串，以构成复杂的查询.

步骤1输出列

我会将具有所需列名的文本文件输出到一个文本文件.让我们将该文件称为"columnnames.csv".如果需要，您甚至可以创建固定列名称文件的存储库.

第2步输出数据

使用bcp像以前一样输出数据.让我们将该输出称为"data.csv"

第3步合并两个文件

您可以使用此简单的批处理命令来合并数据

copy /b columnnames.csv+data.csv combined.csv

或

type columnnames.csv data.csv > combined.csv

有用的资源

如何针对获取列名的模式.

I want to rename the column name in the select clause of a bcp queryout command.I've tried the below variations and none of them work.I want to rename the first and third column to email_address and id respectively.

I am calling the bcp command in a batch script.

bcp "select email as 'email_address', first_name, p_id  as 'id' from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email as [email_address], first_name, p_id  as [id] from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email 'email_address', first_name, p_id 'id' from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,
bcp "select email email_address, first_name, p_id id from table_name" queryout 15Days.txt -c -Sservername -Uusername -Ppassword -t,

Can someone point me to towards the right solution?

解决方案

If you looked at the text files produced by bcp you'll notice column names are not exported. Only data is exported.

The link Alex posted describes a way to add the column names to the output.You are actually adding the fields as first data column to your data using UNION. I recommend against it, because it requires casting all fields as strings making for a complex query.

Step 1 Output the columns

I would output a text file with the desired column names to one text file. Lets call that file "columnnames.csv". You may even create a repository of fixed column name files if need be.

Step 2 Output the data

Use bcp to output the data as you did before. Lets call that output "data.csv"

Step 3 Combine the two files

You can use this simple batch command to combine the data

copy /b columnnames.csv+data.csv combined.csv

or

type columnnames.csv data.csv > combined.csv

Helpful resources

How to query against the schema to get the column names.

这篇关于bcp查询SQL Server的自定义列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！