mssqlserver 复制拷贝附件sql脚本（一）

说明：以下脚本利用mssql实现附件从一个盘复制到另外一个盘（ps：数据库、原始附件所在位置、导出附件存放位置在同一台服务器）

 

--数据库、原始附件所在位置、导出附件存放位置都在同一个服务器

declare @outputDir varchar(500)
declare @oriDir varchar(500)
declare @serverUrl varchar(500)
declare @serverPwd varchar(500)
declare @serverUser varchar(500)
declare @cmd varchar(4000)
declare @desDir varchar(500)
declare @folder varchar(500)

declare @modeid int
declare @fileid int
declare @filename varchar(500)
declare @filePath varchar(500)

declare @rowid int
declare @xh int
declare @zihao varchar(1000)
declare @title varchar(1000)


--附件所在目录（附件所在目录与数据库在同一台服务器）
Set @oriDir='d:\FileSite'
--要导出的目录（导出附件的存放位置）
Set @outputDir='i:\CopyFileSite\Test'

 

select rowid=row_number() over(partition by kp.xh order by kp.xh),kp.xh,kp.docid,kp.zihao,kp.title
 ,att.filename,att.modeid,att.fileid,att.filepath
into ##temp
from kp
 join fileTable att on att.modeid=kp.docid
order by kp.xh
 

declare cur cursor for
 select xh,rowid,zihao,title,modeid,fileid,fileName,FilePath from ##temp
open cur

fetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepath
while @@fetch_status=0
Begin
 --if charindex(char(13), @filename)>0 or charindex(char(10), @filename)>0
 --begin
  set @zihao=replace(@zihao,char(9),'')
  set @zihao=replace(@zihao,char(10),'')
  set @zihao=replace(@zihao,char(11),'')
  set @zihao=replace(@zihao,char(12),'')
  set @zihao=replace(@zihao,char(13),'')
  set @zihao=replace(@zihao,'\','')
  set @zihao=replace(@zihao,'/','')
  set @zihao=replace(@zihao,':','')
  set @zihao=replace(@zihao,'*','')
  set @zihao=replace(@zihao,'?','')
  set @zihao=replace(@zihao,'"','')
  set @zihao=replace(@zihao,'<','')
  set @zihao=replace(@zihao,'>','')
  set @zihao=replace(@zihao,'|','')

  set @filename=replace(@filename,char(9),'')
  set @filename=replace(@filename,char(10),'')
  set @filename=replace(@filename,char(11),'')
  set @filename=replace(@filename,char(12),'')
  set @filename=replace(@filename,char(13),'')
  set @filename=replace(@filename,'\','')
  set @filename=replace(@filename,'/','')
  set @filename=replace(@filename,':','')
  set @filename=replace(@filename,'*','')
  set @filename=replace(@filename,'?','')
  set @filename=replace(@filename,'"','')
  set @filename=replace(@filename,'<','')
  set @filename=replace(@filename,'>','')
  set @filename=replace(@filename,'|','')
  
  set @title=replace(@title,char(9),'')
  set @title=replace(@title,char(10),'')
  set @title=replace(@title,char(11),'')
  set @title=replace(@title,char(12),'')
  set @title=replace(@title,char(13),'')
  set @title=replace(@title,'\','')
  set @title=replace(@title,'/','')
  set @title=replace(@title,':','')
  set @title=replace(@title,'*','')
  set @title=replace(@title,'?','')
  set @title=replace(@title,'"','')
  set @title=replace(@title,'<','')
  set @title=replace(@title,'>','')
  set @title=replace(@title,'|','')

 --去除加急的网页符号
  set @title=replace(@title,'[font color=red','')
  set @title=replace(@title,'font]','_')
 --end 
 if @title is null or @title=''
  begin
   set @title='无标题'
  end
 
 --序号+docid+文号+标题
 --i:\CopyFileSite\Test\1_29076_ 中国〔2009〕3号_关于附件导出脚本的测试
 set @folder=rtrim(cast(@xh as varchar) + '_' + cast(@modeid as varchar) + '_' + @zihao + '_' +

@title)
 
 set @cmd='md "'+@outputDir + '\' + @folder +'"' 
 EXEC master..xp_cmdshell  @cmd,no_output
 --print @cmd 
 
 set @cmd= 'copy  "' +@oriDir +replace(@filePath,'/','\')+'"' + ' "' +@outputDir +'\'+ @folder

+'\'+cast(@rowid as varchar)+'_'+@filename+'"'
 EXEC master..xp_cmdshell   @cmd
 --print  @cmd

 set @cmd=''
fetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepath
End
close cur
deallocate cur


drop table ##temp