求SQL语句,把查询出来的数据从SQL导出成TXT文件

[fancydong]

把查询出来的数据从SQL导出成TXT文件到指定路径下

SELECT convert(char(20),isnull(item_no,''))
+convert(char(20),isnull(item_subname,''))
+convert(char(20),isnull(item_size,''))
+case when convert(int,round(price,2)*100)%100=0 then convert(char(9),convert(dec(9,0),price)) when convert(int,round(price,2)*100)%10=0 then convert(char(9),convert(dec(8,1),price)) else convert(char(9),convert(dec(7,2),price)) end+case when convert(int,round(sale_price,2)*100)%100=0 then convert(char(9),convert(dec(9,0),sale_price)) when convert(int,round(sale_price,2)*100)%10=0 then convert(char(9),convert(dec(8,1),sale_price)) else convert(char(9),convert(dec(7,2),sale_price)) end+convert(char(8),item_clsno)+'0 ','',''
FROM t_bd_item_info
where item_clsno like '%'

[liangCK]

bcp

[areswang]

帮顶+学习！

[liangCK]

exec master..xp_cmdshell 'bcp "你的sql" queryout 路径 -c -U"用户" -P"密码"'

[kk19840210]

exec master..xp_cmdshell'bcp "select * from test.dbo.ttt" queryout c:\1.txt -c -T'


exec master..xp_cmdshell'bcp "select * from 数据库.所有者.表名" queryout c:\1.txt -c -T'

[fancydong]

exec hbposv6..xp_cmdshell 'bcp

SELECT convert(char(20),isnull(item_no,''))
+convert(char(20),isnull(item_subname,''))
+convert(char(20),isnull(item_size,''))
+case when convert(int,round(price,2)*100)%100= 0
then convert(char(9),convert(dec(9,0),price))
when convert(int,round(price,2)*100)%10=0 then
convert(char(9),convert(dec(8,1),price))
else convert(char(9),convert(dec(7,2),price))
end+case when convert(int,round(sale_price,2)*100)%100=0
then convert(char(9),convert(dec(9,0),sale_price))
when convert(int,round(sale_price,2)*100)%10=0
then convert(char(9),convert(dec(8,1),sale_price))
else convert(char(9),convert(dec(7,2),sale_price))
end+convert(char(8),item_clsno)+'0 ','',''
FROM t_bd_item_info
where item_clsno like '%'

queryout c:\1.txt -c -Usa -P '

这样写后,提示错误
服务器: 消息 170，级别 15，状态 1，行 16
第 16 行: '0' 附近有语法错误。

[liangCK]

注意%号.

[fancydong]

exec hbposv6..xp_cmdshell 'bcp

SELECT convert(char(20),isnull(item_no,''))
+convert(char(20),isnull(item_subname,''))
+convert(char(20),isnull(item_size,''))
+case when convert(int,round(price,2)*100)%100= 0
then convert(char(9),convert(dec(9,0),price))
when convert(int,round(price,2)*100)%10=0 then
convert(char(9),convert(dec(8,1),price))
else convert(char(9),convert(dec(7,2),price))
end+case when convert(int,round(sale_price,2)*100)%100=0
then convert(char(9),convert(dec(9,0),sale_price))
when convert(int,round(sale_price,2)*100)%10=0
then convert(char(9),convert(dec(8,1),sale_price))
else convert(char(9),convert(dec(7,2),sale_price))
end+convert(char(8),item_clsno)+'0 ','',''
FROM t_bd_item_info
where item_clsno like '%'

queryout c:\1.txt -c -Usa -P '

[liangCK]

xp_cmdshell在master库里.

[fancydong]

服务器: 消息 170，级别 15，状态 1，行 16
第 16 行: '0' 附近有语法错误。
提示 0附近有语法错误啊

[liangCK]

这样试试.

exec master..xp_cmdshell 
  'bcp 
    "SELECT convert(char(20),isnull(item_no,''))   
           +convert(char(20),isnull(item_subname,''))   
           +convert(char(20),isnull(item_size,''))   
           +case 
              when convert(int,round(price,2)*100)%100= 0       
                 then convert(char(9),convert(dec(9,0),price))       
              when convert(int,round(price,2)*100)%10=0       
                 then convert(char(9),convert(dec(8,1),price))     
              else convert(char(9),convert(dec(7,2),price)) end
           +case       
              when convert(int,round(sale_price,2)*100)%100=0     
                 then convert(char(9),convert(dec(9,0),sale_price))       
              when convert(int,round(sale_price,2)*100)%10=0       
                 then convert(char(9),convert(dec(8,1),sale_price))       
              else convert(char(9),convert(dec(7,2),sale_price)) end
           +convert(char(8),item_clsno)+''0'','',''       
     FROM t_bd_item_info       
     where item_clsno like ''%''"     
queryout c:\1.txt -c -U"sa" -P"" ' 

[fancydong]

用法: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL

[liangCK]

啥意思?.
自问自答?

[fancydong]

我在查询分析器中执行以下语句,并没有生成文件
exec master..xp_cmdshell
'bcp
"SELECT convert(char(20),isnull(item_no,''))
+convert(char(20),isnull(item_subname,''))
+convert(char(20),isnull(item_size,''))
+case
when convert(int,round(price,2)*100)%100= 0
then convert(char(9),convert(dec(9,0),price))
when convert(int,round(price,2)*100)%10=0
then convert(char(9),convert(dec(8,1),price))
else convert(char(9),convert(dec(7,2),price)) end
+case
when convert(int,round(sale_price,2)*100)%100=0
then convert(char(9),convert(dec(9,0),sale_price))
when convert(int,round(sale_price,2)*100)%10=0
then convert(char(9),convert(dec(8,1),sale_price))
else convert(char(9),convert(dec(7,2),sale_price)) end
+convert(char(8),item_clsno)+''0'','',''
FROM t_bd_item_info
where item_clsno like ''%''"
queryout c:\1.txt -c -U"sa" -P"" '

[issacp]

你的数据库是安装在服务器上，而你在客户端执行的话，生成的文件应该是在服务器的C盘上

[fancydong]

在服务器上执行的

[谁是谁的谁]

路过,留个脚印...