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:CopyFileSiteTest'

 

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),char(11),char(12),char(13),'','/','?1','*','?','"','<','>','|','')

  set @filename=replace(@filename,'')
  set @filename=replace(@filename,'')
  
  set @title=replace(@title,'')
  set @title=replace(@title,'')

 --去除加急的网页符号
  set @title=replace(@title,'[font color=red','font]','_')
 --end 
 if @title is null or @title=''
  begin
   set @title='无标题'
  end
 
 --序号+docid+文号+标题
 --i:CopyFileSiteTest1_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,@filepath
End
close cur
deallocate cur


drop table ##temp

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!