-----先建一个跟目标表一样格式的表xx declare @no varchar(10) , @name varchar(10) , @lb varchar(10) , @sql varchar(800) , @int int set @int=1 declare kk cursor for select bm_no,bm_name,lb from k0503bm open kk fetch next from kk into @no,@name,@lb while @@fetch_status=0 begin set @sql='insert into xx '+'select '+''''+@no+''''+','+''''+@name+''''+','+''''+@lb+'''' exec(@sql) set @sql='EXEC master..xp_cmdshell ''bcp "kq_yd..xx" out c:\DT'+convert(varchar(10),@int)+'.txt -c -S. -Usa -P''' exec(@sql) set @sql='truncate table xx' exec(@sql) set @int=@int+1 fetch next from kk into @no,@name,@lb end close kk deallocate kk
--处理示例--测试表 create table tb(id varchar(50) primary key,detail text) insert tb select 'aaa','11111' union all select 'bbb','43424' union all select 'ccc','324234' go--处理的存储过程 create proc p_export @path nvarchar(1000) --导出的文本文件保存的目录 as declare @s nvarchar(4000) if isnull(@path,'')='' set @path='c:\' else if right(@path,1)<>'\' set @path=@path+'\'--用游标构建每条记录的bcp导出语句,BCP的语法参考sql联机帮助 declare tb cursor local for select 'BCP "select detail from ' +quotename(db_name()) +'..tb where id=' +quotename(id,N'''') +'" queryout "'+@path +id+'.txt" /T /w' from tb open tb fetch tb into @s while @@fetch_status=0 begin --调用xp_cmdshell存储过程执行bcp进行导出处理 exec master..xp_cmdshell @s,no_output --为方便楼主观察bcp语句,将处理语句打印出来 print(@s) fetch tb into @s end close tb deallocate tb go--调用 exec p_export 'c:\' go--删除测试 drop table tb drop proc p_export
-----先建一个跟目标表一样格式的表xx
declare @no varchar(10)
, @name varchar(10)
, @lb varchar(10)
, @sql varchar(800)
, @int int
set @int=1
declare kk cursor for select bm_no,bm_name,lb from k0503bm
open kk
fetch next from kk into @no,@name,@lb
while @@fetch_status=0
begin
set @sql='insert into xx '+'select '+''''+@no+''''+','+''''+@name+''''+','+''''+@lb+''''
exec(@sql)
set @sql='EXEC master..xp_cmdshell ''bcp "kq_yd..xx" out c:\DT'+convert(varchar(10),@int)+'.txt -c -S. -Usa -P'''
exec(@sql)
set @sql='truncate table xx'
exec(@sql)
set @int=@int+1
fetch next from kk into @no,@name,@lb
end
close kk
deallocate kk
另外,可不可以帮我写成一个完整的存储过程
create table tb(id varchar(50) primary key,detail text)
insert tb select 'aaa','11111'
union all select 'bbb','43424'
union all select 'ccc','324234'
go--处理的存储过程
create proc p_export
@path nvarchar(1000) --导出的文本文件保存的目录
as
declare @s nvarchar(4000)
if isnull(@path,'')='' set @path='c:\'
else if right(@path,1)<>'\' set @path=@path+'\'--用游标构建每条记录的bcp导出语句,BCP的语法参考sql联机帮助
declare tb cursor local
for
select 'BCP "select detail from '
+quotename(db_name())
+'..tb where id='
+quotename(id,N'''')
+'" queryout "'+@path
+id+'.txt" /T /w'
from tb
open tb
fetch tb into @s
while @@fetch_status=0
begin
--调用xp_cmdshell存储过程执行bcp进行导出处理
exec master..xp_cmdshell @s,no_output
--为方便楼主观察bcp语句,将处理语句打印出来
print(@s)
fetch tb into @s
end
close tb
deallocate tb
go--调用
exec p_export 'c:\'
go--删除测试
drop table tb
drop proc p_export