Sql游标不处理所有文件 [英] Sql cursor not processing all files

问题描述

下面的光标适用于文件夹中的前两个文件。



我需要它来处理文件夹中有多少文件 - 而不仅仅是冷杉两个文件。



我想在复制第一个文件后取出文件夹中剩下的所有文件，然后将它们移到子文件夹中。光标只会将文件夹中的一个文件移动到子文件夹。



我的错误在哪里？

The below cursor works for the first two files in the folder.

I need it to work for however many files there are in the folder - not just the firs two files.

I want to take any files that are left in the folder after the first file has been copied, and move them to the subsubfolder. The cursor will only move one file in the folder to the subsubfolder.

Where is my error?

declare @cmdstring varchar(1000)
declare @PATH VARCHAR(256)
declare @Command varchar(1024) =  @Path + ' /A-D  /B'
declare @createdate datetime
declare @filename VARCHAR(500)
INSERT INTO #mytemptable
EXEC MASTER..xp_cmdshell @Command

   DECLARE Folder_FileList CURSOR 
    FOR  SELECT	
			Filename
			from #mytemptable
			 
    OPEN Folder_FileList;


    FETCH NEXT FROM Folder_FileList INTO @Filename

        BEGIN			
		SET NOCOUNT ON;	
		
	set @createdate = GETDATE()

set @cmdstring = 'copy "\\servername\c$\MainFolder\Subfolder\' + @filename  + '"  "C:\FinalFolder\FinalSubFolder\' + @filename + ' "'
exec master..xp_cmdshell @cmdstring,no_output


set @cmdstring = 'C:\FinalFolder\FinalSubFolder\' + @filename + '"'+ ' ' + '" "\\servername\c$\MainFolder\Subfolder\SSubFolder\'+ @filename +'_'+ CONVERT(varchar(10),getdate(),112) + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108),':','') + '.xls"'
exec master..xp_cmdshell @cmdstring,no_output
 
 
set @cmdstring = 'DEL "C:\FinalFolder\FinalSubFolder\' + @filename 
exec master..xp_cmdshell @cmdstring,no_output

set @cmdstring = 'DEL "\\servername\c$\MainFolder\Subfolder\' + @filename 
exec master..xp_cmdshell @cmdstring
 
     FETCH NEXT FROM Folder_FileList INTO @Filename
         
     
set @cmdstring = 'copy "\\servername\c$\MainFolder\Subfolder\' + @filename  + '"  "\\servername\c$\MainFolder\Subfolder\SSubFolder\' + @filename + ' "'
exec master..xp_cmdshell @cmdstring,no_output

set @cmdstring = 'DEL "\\servername\c$\MainFolder\Subfolder\' + @filename 
exec master..xp_cmdshell @cmdstring,no_output


 END
 

CLOSE Folder_FileList
DEALLOCATE Folder_FileList

我尝试过：



MSDN，谷歌搜索，stackoverflow，各种博客

What I have tried:

MSDN, google search, stackoverflow, various blogs

推荐答案

\ MainFolder \Subfolder \' + @ filename + ' C： \FinalFolder \FinalSubFolder \' + @ filename + ' '
exec master..xp_cmdshell @ cmdstring ，no_output


set @ cmdstring = ' C：\FinalFolder \FinalSubFolder \' + @ filename + ' < span class =code-string>' + ' ' + ' \\servername\c

\MainFolder\Subfolder\' + @filename + '" "C:\FinalFolder\FinalSubFolder\' + @filename + ' "' exec master..xp_cmdshell @cmdstring,no_output set @cmdstring = 'C:\FinalFolder\FinalSubFolder\' + @filename + '"'+ ' ' + '" "\\servername\c

\ MainFolder \Subfolder \\ \\ SSubFolder \' + @ filename + ' _' + CONVERT （ varchar （ 10 ），getdate（）， 112 ）+ REPLACE（ CONVERT （ VARCHAR （ 8 ），GETDATE（ ）， 108 ），' ：' ，' '）+ ' 。xls'
exec master..xp_cmdshell @ cmdstring ，no_output


set @ cmdstring = ' DELC：\FinalFolder \FinalSubFolder \' + @ filename
exec master..xp_cmdshell @ cmdstring ， no_output

set @ cmdstring = ' DEL\\servername\c

\MainFolder\Subfolder\SSubFolder\'+ @filename +'_'+ CONVERT(varchar(10),getdate(),112) + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108),':','') + '.xls"' exec master..xp_cmdshell @cmdstring,no_output set @cmdstring = 'DEL "C:\FinalFolder\FinalSubFolder\' + @filename exec master..xp_cmdshell @cmdstring,no_output set @cmdstring = 'DEL "\\servername\c

\ MainFolder \\ \\ _subfolder \' + @ filename
exec master..xp_cmdshell < span class =code-sdkkeyword> @ cmdstring

FETCH NEXT FROM Folder_FileList INTO @ Filename


set @ cmdstring = ' copy\\servername\c

\MainFolder\Subfolder\' + @filename exec master..xp_cmdshell @cmdstring FETCH NEXT FROM Folder_FileList INTO @Filename set @cmdstring = 'copy "\\servername\c

这篇关于Sql游标不处理所有文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！