exec xp_cmdshell bcp语法 [英] exec xp_cmdshell bcp syntax

问题描述

在SQL Server Management Studio中，我似乎找不到使用exec xp_cmdshell bcp导出具有列名的数据的正确语法.我尝试了以下变体

I can't seem to find the right syntax to export data with column names using exec xp_cmdshell bcp in sql server management studio.I've tried the below variations

EXEC xp_cmdshell bcp "select "a_id","b_id","c_id" union select a_id,b_id,c_id from  tablename out 
"\\network_path\a.txt" -c -Uusername -Ppassword -Sservername"

还有

EXEC xp_cmdshell bcp 'select 'a_id','b_id','c_id' union select a_id,b_id,c_id from  tablename out 
'\\network_path\a.txt' -c -Uusername -Ppassword -Sservername'

还有

EXEC xp_cmdshell bcp 'select "a_id","b_id","c_id" union select a_id,b_id,c_id from  tablename out 
"\\network_path\a.txt" -c -Uusername -Ppassword -Sservername'

还有

EXEC xp_cmdshell bcp "select 'a_id','b_id','c_id' union select a_id,b_id,c_id from  tablename out 
'\\network_path\a.txt' -c -Uusername -Ppassword -Sservername"

我已经成功使用以下命令导出了表，但是我也需要列名.

I have successfully used the below command to export the table,however I also need the column names.

bcp tablename out "\\network_path\a_test.txt" -c -Uusername -Ppassword -Sservername'

推荐答案

好吧，您实际上需要对标题行进行排序，使其显示在顶部.这应该处理

Ok you'll actually need to order the header row so it appears on top. This should handle it

EXEC xp_cmdshell 'bcp "select * from (select ''a_id'' as a_id,''b_id'' as b_id,''c_id'' as c_id union select a_id,b_id,c_id from tablename)q order by case a_id when ''a_id'' then 0 ELSE 1 END" queryout "\\networkpath\a.txt" -c -Uusername -Ppassword -Sservername -ddatabasename'

这篇关于exec xp_cmdshell bcp语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！