SQL Server 2008 上的批量删除（是否有类似批量复制 (bcp) 之类的用于删除数据的功能？）

Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)

提问by masoud ramezani

Is there any solution for bulk delete in SQL Server?

SQL Server 中是否有批量删除的解决方案？

I can't use TRUNCATE because I want to use WHERE for limiting the rows in action.

我不能使用 TRUNCATE，因为我想使用 WHERE 来限制操作中的行。

Is there anything like Bulk Copy (bcp) for delete data?

是否有用于删除数据的批量复制（bcp）之类的东西？

回答by gbn

No.

不。

You want a DELETE with a WHERE clause: this is standard SQL.

您需要带有 WHERE 子句的 DELETE：这是标准 SQL。

What you can do is batch deletes like this:

您可以做的是批量删除，如下所示：

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

Or if you want to remove a very high percentage of rows...

或者，如果您想删除非常高百分比的行...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..opposite condition..
TRUNCATE TABLE MyTable
INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable

回答by Eyal Z.

You can do a couple of things if you want to delete part of your table and not TRUNCATEit.

如果您想删除表格的一部分而不是TRUNCATE它，您可以做一些事情。

you could select a part of the table into a new table, then switch the two, like so:

您可以选择表格的一部分到一个新表格中，然后切换两者，如下所示：

SELECT *
INTO tmp_MyTable
FROM MyTable
WHERE Key='Value'

IF @@ROWCOUNT > 0
BEGIN
    EXEC sp_rename MyTable, old_MyTable, NULL
    EXEC sp_rename tmp_MyTable, MyTable, NULL
    TRUNCATE old_MyTable 
END

Secondly, if you're using Partitioning, you can create an identical (empty) table on the same partition scheme.. and if the table is partitioned according to your archiving / purging logic, you can move one partition block from your main table to the new table and then truncate the new table.. For example:

其次，如果您使用分区，您可以在相同的分区方案上创建一个相同的（空）表..如果该表是根据您的归档/清除逻辑进行分区的，您可以将一个分区块从主表移动到新表，然后截断新表.. 例如：

ALTER TABLE MyTable
SWITCH PARTITION 15 TO purge_MyTable PARTITION 2 
GO; 

TRUNCATE TABLE purge_MyTable 

Ps. Partitions are available in SQL 2005/08 Ent.

附言。SQL 2005/08 Ent 中提供了分区。

Hope this helps!

希望这可以帮助！

回答by Eyal Z.

Sychare Jedko,

Sychare Jedko，

The advantage of TRUNCATE is to avoid logging every single delete in the log file. a TRUNCATE statement will create a single entry (in log) for the entire batch.

TRUNCATE 的优点是避免在日志文件中记录每次删除。TRUNCATE 语句将为整个批处理创建单个条目（在日志中）。

回答by MichaelBolton

Just ran into a similar issue working on a staging table that had issues scaling with proper locks.

刚刚在临时表上遇到了类似的问题，该问题在使用适当的锁进行缩放时遇到了问题。

Since the relevant table is only referenced in one location for us, we simply replaced that reference with a query for the dynamic table name, which is created with a "select into" similar to what gbn suggested.

由于相关表仅在我们的一个位置被引用，我们简单地用动态表名的查询替换了该引用，该查询是使用类似于 gbn 建议的“select into”创建的。

This is maintainable because the staging table is only referenced in one place in code, and the expense of the extra database call along with table creation is justified in a warehousing context. If you have only a few hundred records or reference the table in your code numerous times, then this approach may not work.

这是可维护的，因为临时表仅在代码中的一个地方引用，并且额外的数据库调用以及表创建的费用在仓储上下文中是合理的。如果您只有几百条记录或在代码中多次引用该表，则此方法可能不起作用。

回答by Morten Snedker

When handling million of rows I prefer having a WHERE statement and using SELECT INTO a copy table, delete the original table and rename the copy (back to original name).

在处理数百万行时，我更喜欢使用 WHERE 语句并使用 SELECT INTO 复制表，删除原始表并重命名副本（回到原始名称）。

Though, you should have stuff like (FK)keys, constraints etc in mind. But using this method you avoid badazz-size of log, and you avoid a huge time consumption of deleting in chunks.

不过，您应该考虑（FK）键、约束等内容。但是使用这种方法可以避免日志的badazz-size，并且避免了大量删除块的时间消耗。

/Snedker

/斯内克

回答by Icarus

check this

检查这个

1. article from MSDN Delete_a_Huge_Amount_of_Data_from
2. Information on Recovery Models
3. and View or Change the Recovery Model of a Database

1. 文章来自 MSDN Delete_a_Huge_Amount_of_Data_from
2. 有关恢复模型的信息
3. 并查看或更改数据库的恢复模式