逐记录导出text/ntext字段值为文本文件

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

/*--原帖地址:http://community.csdn.net/Expert/topic/3851/3851741.xml?temp=.4726831--*/

--测试数据createtabletb(idvarchar(50)primarykey,detailtext)inserttbselect'aaa','11111'unionallselect'bbb','43424'unionallselect'ccc','324234'

/*--处理要求

 把上述表中的detail字段导出为文本文件,要求每条记录一个文件,文件名为id+.txt 即上述表中的数据要求导出为aaa.txt,bbb.txt,ccc.txt--*/go

--处理的存储过程createprocp_export@pathnvarchar(1000) --导出的文本文件保存的目录asdeclare@snvarchar(4000)ifisnull(@path,'')=''set@path='c:/'elseifright(@path,1)<>'/'set@path=@path+'/'

--用游标构建每条记录的bcp导出语句,BCP的语法参考sql联机帮助declaretbcursorlocalforselect'BCP"selectdetailfrom' +quotename(db_name()) +'..tbwhereid=' +quotename(id,N'''') +'"queryout"'+@path +id+'.txt"/T/w'fromtbopentbfetchtbinto@swhile@@fetch_status=0begin --调用xp_cmdshell存储过程执行bcp进行导出处理 execmaster..xp_cmdshell@s,no_output fetchtbinto@sendclosetbdeallocatetbgo

--调用execp_export'c:/'go

--删除测试droptabletbdropprocp_export
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>