IDC资讯中心
/*–原帖地址:http://community.csdn.net/expert/topic/3851/3851741.xml?temp=.4726831–*/
–测试数据create table tb(id varchar(50) primary key,detail text)insert tb select aaa,11111union all select bbb,43424union all select ccc,324234
/*–处理要求
把上述表中的detail字段导出为文本文件,要求每条记录一个文件,文件名为id+.txt 即上述表中的数据要求导出为 aaa.txt,bbb.txt,ccc.txt–*/go
–处理的存储过程create proc p_export@path nvarchar(1000) –导出的文本文件保存的目录asdeclare @s nvarchar(4000)if isnull(@path,)= set @path=c:\else if right(@path,1)<>\ set @path=@path+\
–用游标构建每条记录的bcp导出语句,bcp的语法参考sql联机帮助declare tb cursor localforselect bcp "select detail from +quotename(db_name()) +..tb where id= +quotename(id,n) +" queryout "+@path +id+.txt" /t /wfrom tbopen tb fetch tb into @swhile @@fetch_status=0begin –调用xp_cmdshell存储过程执行bcp进行导出处理 exec master..xp_cmdshell @s,no_output fetch tb into @sendclose tbdeallocate tbgo
–调用exec p_export c:\go
–删除测试drop table tbdrop proc p_export
