dbWriter.com SQL archive files code
Archive or move files using SQL server. This code will move
files from one directory to another. It can be set as a scheduled
job:
declare @Path varchar(255)
declare @MoveTO varchar(255)
declare @sql varchar (255)
declare @name varchar (255)
declare @ext varchar (10)
declare @eID int
declare @eMaxID int
set @Path = 'Q:\DirectoryToArchive\'
set @MoveTO = 'Q:\TheArchiveDirectory\'
set @ext = '"*.asp"' -- requires the double
quotes inside of single quotes!!
create table #tempDB
(
theFiles varchar(255) ,
ID int identity (1,1)
)
select @sql = 'dir /b/a-d ' + @Path
select @sql = @sql + @ext
insert #tempDB exec master..xp_cmdshell @sql
delete #tempDB
where theFiles is null OR theFiles = 'File Not Found'
OR theFiles = 'The system cannot find the file specified.'
select * from #tempDB
-- loop round
select @eID = 0 ,
@eMaxID = coalesce(max(ID), 0)
from #tempDB
while @eID < @eMaxID
begin
select @eID = min(ID) from #tempDB where ID > @eID
select @Name = theFiles from #tempDB where ID = @eID
select @sql = 'move ' + @Path + '"' + @Name + '" ' + @MoveTO
exec master..xp_cmdshell @sql, no_output
end
drop table #tempDB
|
|
