在sql中处理文件的操作

myfavorite

DECLARE   @Path   nvarchar ( 200 )
SET   @Path   =   ' C:\Inetpub\wwwroot\kaoq\txt\ '
IF   RIGHT ( @Path ,  1 )  <>   ' \ '
     SET   @Path   =   @Path   +   ' \ '
IF   OBJECT_ID ( ' tempdb..# ' )  IS   NOT   NULL
     DROP   TABLE  #
CREATE   TABLE  #
(
    id  int   IDENTITY ,
    directory  nvarchar ( 260 ),
    depth  int ,
    IsFile  bit
)
INSERT  #  EXEC  master.dbo.xp_dirtree  @path   =   @path , @depth   =   0 , @file   =   1


declare   @strPath   varchar ( 200 )
declare  cur_file  cursor   for   select   ' C:\Inetpub\wwwroot\kaoq\txt\ '   +  directory  from  #  order   by  directory
open  cur_file
fetch   next   from  cur_file  into   @strPath
while   @@fetch_status = 0  
     begin
         declare   @o   int , @f   int , @t   int , @ret   int
         declare   @card8   varchar ( 1000 )
         declare   @date8   varchar ( 1000 )
         declare   @time8   varchar ( 1000 )


         exec   @ret = sp_OACreate  ' Scripting.FileSystemObject ' , @o  out
         exec   @ret = sp_oamethod  @o , ' OpenTextFile ' , @f  out, @strPath , 1
         exec   @ret = sp_oamethod  @f , ' readline ' , @card8  out
         exec   @ret = sp_oamethod  @f , ' readline ' , @date8  out
         exec   @ret = sp_oamethod  @f , ' readline ' , @time8  out
         -- '''''''处理数据
         exec   @ret = sp_OADestroy  @f
         exec   @ret = sp_OAMethod  @o ,  ' DeleteFile ' ,  NULL ,  @strPath
         exec   @ret   =  sp_OADestroy  @o

         fetch   next   from  cur_file  into   @strpath
     end
close  cur_file
deallocate  cur_file

转载于:https://www.cnblogs.com/MyFavorite/archive/2007/10/13/923003.html