更新时间:2023-02-07 13:06:03
如果查看bcp
生成的文本文件,您会注意到列名没有导出.仅导出数据.
Alex发表的链接描述了一种在输出中添加列名称的方法.实际上,您是使用UNION将字段作为第一个数据列添加到数据中的.我建议不要这样做,因为它要求将所有字段都强制转换为字符串,以构成复杂的查询.
我会将具有所需列名的文本文件输出到一个文本文件.让我们将该文件称为"columnnames.csv".如果需要,您甚至可以创建固定列名称文件的存储库.
使用bcp
像以前一样输出数据.让我们将该输出称为"data.csv"
您可以使用此简单的批处理命令来合并数据
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.
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.
Use bcp
to output the data as you did before. Lets call that output "data.csv"
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