SQL Server 2008 上的批量删除(是否有类似批量复制 (bcp) 之类的用于删除数据的功能?) [英] Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)

问题描述

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

Is there any solution for bulk delete in SQL Server?

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

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

是否有类似批量复制 (bcp) 之类的东西来删除数据?

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

推荐答案

没有

您需要带有 WHERE 子句的 DELETE:这是标准 SQL.

You want a DELETE with a WHERE clause: this is standard 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

这篇关于SQL Server 2008 上的批量删除(是否有类似批量复制 (bcp) 之类的用于删除数据的功能?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！