Export Text/ntext field value per record as a text file

/*--Original post address: http://community.csdn.net/expert/topic/3851/3851741.xml?temp=.4726831--*/

--Test data CREATE TABLE TB (ID varchar (m) primary key,detail text) Insert TB Select ' AAA ', ' 11111 ' union ALL Select ' BBB ', ' 43424 ' u Nion all SELECT ' CCC ', ' 324234 '

/*--Processing Requirements

The detail field in the above table is exported as a text file, requiring that each record be a file named Id+.txt the data in the above table is exported to Aaa.txt,bbb.txt,ccc.txt--*/go

--Processed stored procedures create proc P_export@path nvarchar (1000)--Exported text file saved directory asdeclare @s nvarchar (4000) if IsNull (@path, ') = ' SET @ Path= ' C:\ ' else if Right (@path, 1) <> ' Set @path = @path + ' \ '

--To build a BCP export statement for each record with a cursor, the syntax reference for BCP is SQL online help declare TB cursor localforselect ' BCP ' select Detail from ' +quotename (db_name ()) + '. TB where id= ' +quotename (id,n ' ") + ' queryout ' + @path +id+ '. txt '/t/w ' from Tbopen TB fetch TB into @swhile @ @fetch_sta Tus=0begin-Calls xp_cmdshell stored procedures to perform BCP for export processing exec master. xp_cmdshell @s,no_output fetch TB into @sendclose tbdeallocate tbgo

--Call exec p_export ' C:\ ' Go

--Delete test drop table Tbdrop proc P_export