/ * - Originally: http://community.9cbs.net/expert/topic/3851/3851741.xml? Temp = .4726831 - * /
- Test data create Table TB (id varchar (50) primary key, detail text) Insert TB SELECT 'AAA', '1111' ELON All SELECT 'BBB', '43424'Union All Slect' CCC ',' 324234 '
/ * - Treatment requirements
Export the Detail field in the above table to text files, require each record, the file name is ID .txt, that is, the data requirements in the above table are exported to AAA.TXT, BBB.TXT, CCC.TXT - * / GO
- Processing stored procedure CREATE P_EXPORT @ path nvarchar (1000) - Export text file saved directory asclare @s nvarchar (4000) if isnull (@path, '') = '' set @ path = 'C: / 'else if right (@ Path, 1) <>' / 'set @ path = @ Path ' / '
- Build each recorded BCP export statement, BCP syntax reference 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_status = 0begin - Call the XP_cmdshell stored procedure to perform BCP Export processing exec master..xp_cmdshell @ s, no_output fetch tb @sendclose TBDEAllocate TBGO
- Call EXEC P_EXPORT 'C: /' Go
- Delete TABLE TBDROP PROC P_EXPORT
