使用 T-SQL 从文件夹中删除多个文件而不使用游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5153342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete multiple files from folder using T-SQL without using cursor
提问by Kashif
I am writing a cleanup script. This script will run on weekend and clean up the db. Tables are related to Eamils and path of attachments are being stored in table. In cleanup of tables I also have to delete files from folder.
我正在编写一个清理脚本。该脚本将在周末运行并清理数据库。表与电子邮件相关，附件路径存储在表中。在清理表格时，我还必须从文件夹中删除文件。
The path of files is like following.
文件路径如下。
\xxx.xxx.xxx.xxx\EmailAttachments\Some Confirmation for xyz Children Centre_9FW4ZE1C57324B70EC79WZ15FT9FA19E.pdf
I can delete multiple files like following.
我可以删除多个文件，如下所示。
xp_cmdshell 'del c:\xyz.txt, abc.txt'
BUT when I create a CSV from table using FOR XML PATH('') the string cut off at the end. There might be 1000s of rows to delete so I don't want to use cursor to delete files from folder.
但是，当我使用 FOR XML PATH('') 从表创建 CSV 时，字符串在末尾被切断。可能有 1000 行要删除，所以我不想使用光标从文件夹中删除文件。
- How can I delete files from folder without using cursor
- What permissions do I need on network folder to delete files using t-sql from sql server
- 如何在不使用光标的情况下从文件夹中删除文件
- 我需要在网络文件夹上有什么权限才能使用 sql server 中的 t-sql 删除文件
EDIT:I have used cursor and it looks ok, not taking so much time. One problem which I am facing is
编辑：我使用过游标，它看起来不错，不需要太多时间。我面临的一个问题是
The sql server consider file name with space as two files like following statement
xp_cmdshell 'del E:\Standard Invite.doc'
sql server 将带空格的文件名视为两个文件，如以下语句
xp_cmdshell 'del E:\Standard Invite.doc'
throws error
抛出错误
Could Not Find E:\Standard
Could Not Find C:\Windows\system32\Invite.doc
NULL
Thanks.
谢谢。
回答by Rikalous
Personally, I wouldn't worry too much about using a cursor here. Cursors are only 'mostly evil'; as your task isn't a set-based operation a cursor may be the most effective solution.
就个人而言，我不会太担心在这里使用游标。游标只是“主要是邪恶的”；由于您的任务不是基于集合的操作，因此游标可能是最有效的解决方案。
回答by MatBailie
Although you have a comment stating that it will take an "awful lot of time" to use a cursor, in this case the biggest overhead is the actual delete of the file (not the cursor).
尽管您有评论指出使用游标将花费“大量时间”，但在这种情况下，最大的开销是实际删除文件（而不是游标）。
Note: The file deletion is done by the Operation System, not by the RDBMS.
注意：文件删除是由操作系统完成的，而不是由 RDBMS 完成的。
As the delete is being done by calling xp_cmdshell, and because it it a procedure (not a function, etc), you can't call it and pass in a table's contents.
由于删除是通过调用 xp_cmdshell 完成的，并且因为它是一个过程（不是函数等），所以您不能调用它并传入表的内容。
What you could do is build up a string, and execute that. But note, you are limitted to a maximum of 8000 characters in this string. As you have already said that you may have thousands of files, you will certaily not fit it within 8000 characters.
你可以做的是建立一个字符串，然后执行它。但请注意，此字符串中最多只能包含 8000 个字符。正如您已经说过的，您可能有数千个文件，您肯定不会在 8000 个字符内容纳它。
This means that you are going to need a loop no matter what.
这意味着无论如何你都需要一个循环。
DECLARE
@command VARCHAR(8000),
@next_id INT,
@next_file VARCHAR(8000),
@total_len INT
SELECT
@command = 'DEL ',
@total_len = 4
SELECT TOP 1
@next_id = id,
@next_file = file_name + ', '
FROM
table_of_files_to_delete
ORDER BY
id DESC
WHILE (@next_file IS NOT NULL)
BEGIN
WHILE ((@total_len + LEN(@next_file)) <= 8000) AND (@next_file IS NOT NULL)
BEGIN
SELECT
@command = @command + @next_file,
@total_len = @total_len + LEN(@next_file)
SELECT
@next_file = NULL
SELECT TOP 1
@next_id = id,
@next_file = file_name + ', '
FROM
table_of_files_to_delete
WHERE
id < @next_id
ORDER BY
id DESC
END
SET @command = SUBSTRING(@command, 1, @total_len - 2) -- remove the last ', '
EXEC xp_cmdshell @command
SELECT
@command = 'DEL ',
@total_len = 4
END
Not pretty, huh?
不漂亮吧？
What you may be able do, depending on what needs deleting, is to use wild-cards. For example:
根据需要删除的内容，您可以做的是使用通配符。例如：
EXEC xp_cmdshell 'DELETE C:\abc\def\*.txt'
回答by Mikael Eriksson
To delete files with space in name you need to enclose the filename with "
要删除名称中有空格的文件，您需要将文件名用 "
xp_cmdshell 'del "E:\Standard Invite.doc"'
回答by Lorena Pita
DECLARE @deleteSql varchar(500)
,@myPath varchar(500) = '\DestinationFolder\'
SET @deleteSql = 'EXEC master..xp_cmdshell ''del '+@myPath +'*.csv'''
EXEC(@deleteSql)
