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 intdeclare @fileid intdeclare @filename varchar(500)declare @filePath varchar(500)declare @rowid intdeclare @xh intdeclare @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.filepathinto ##tempfrom kp join fileTable att on att.modeid=kp.docidorder by kp.xh declare cur cursor for select xh,rowid,zihao,title,modeid,fileid,fileName,FilePath from ##tempopen curfetch next from cur into @xh,@rowid,@zihao,@title,@modeid,@fileid,@filename,@filepathwhile @@fetch_status=0Begin --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,@filepathEndclose curdeallocate curdrop table ##temp