Sql游标不处理所有文件 [英] Sql cursor not processing all files
本文介绍了Sql游标不处理所有文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
下面的光标适用于文件夹中的前两个文件。
我需要它来处理文件夹中有多少文件 - 而不仅仅是冷杉两个文件。
我想在复制第一个文件后取出文件夹中剩下的所有文件,然后将它们移到子文件夹中。光标只会将文件夹中的一个文件移动到子文件夹。
我的错误在哪里?
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屋!
查看全文