Is There a Bulk Delete Command in SQL Server 2008 Like bcp for Inserts?

Mass Deletion in SQL Server 2008: A Parallel to Bulk Copy for Delete Operations

In this inquiry, the need for a bulk delete operation in SQL Server 2008 arises due to the absence of a direct solution comparable to the Bulk Copy operation (bcp) for data deletion. To address this gap, users seek an alternative that enables mass deletion with the precision of a WHERE clause.

The official response confirms the absence of a specialized bulk delete command in SQL Server. However, it offers practical alternatives to achieve the desired outcome.

Batch Deletes

One approach is to execute batch deletes using the following syntax:

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

This allows you to specify a batch size (xxx) and iteratively delete rows until no more rows remain.

Alternate Deletion Strategy for Deleting Most Rows

In scenarios where a large percentage of rows need to be removed, a more efficient approach is to use the following steps:

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

This involves creating a temporary holding table containing the rows that are not to be deleted and then truncating the main table. Finally, the data from the holding table is reinserted into the main table.

The above is the detailed content of Is There a Bulk Delete Command in SQL Server 2008 Like bcp for Inserts?. For more information, please follow other related articles on the PHP Chinese website!