• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

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

fancydong 2008-02-18 05:08:18
把查询出来的数据从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 '%'
...全文
496 点赞 收藏 16
写回复
16 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
谁是谁的谁 2008-02-19
路过,留个脚印...
回复
fancydong 2008-02-19
在服务器上执行的
回复
issacp 2008-02-19
你的数据库是安装在服务器上,而你在客户端执行的话,生成的文件应该是在服务器的C盘上
回复
fancydong 2008-02-19
我在查询分析器中执行以下语句,并没有生成文件
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"" '
回复
liangCK 2008-02-18
啥意思?.
自问自答?
回复
fancydong 2008-02-18
用法: 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 2008-02-18
这样试试.

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 2008-02-18
服务器: 消息 170,级别 15,状态 1,行 16
第 16 行: '0' 附近有语法错误。

提示 0附近有语法错误啊
回复
liangCK 2008-02-18
xp_cmdshell在master库里.
回复
fancydong 2008-02-18
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 2008-02-18
注意%号.
回复
fancydong 2008-02-18
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' 附近有语法错误。


回复
kk19840210 2008-02-18

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'
回复
liangCK 2008-02-18
exec master..xp_cmdshell 'bcp "你的sql" queryout 路径 -c -U"用户" -P"密码"'
回复
areswang 2008-02-18
帮顶+学习!
回复
liangCK 2008-02-18
bcp
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-02-18 05:08
社区公告
暂无公告