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

Solution 1:

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

Solution 2:

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!