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

Question

Is there any solution for bulk delete in SQL Server?

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

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

Answer 1

The advantage of truncate is resetting any auto-incrementing primary keys to their start value (0). You can do the following to include a where condition:

DELETE FROM tableName WHERE condition

Answer 2

No.

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

Answer 3

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

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.

Hope this helps!

Answer 4

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.