在sql中处理文件的操作
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
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