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

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 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!

Answer 3

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.

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.

Answer 4

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).

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.

/Snedker

Answer 5

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

Answer 6

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.

Answer 7

You can use a loop to delete by chunks:

Here was my solution to delete 100 records at every time:

DECLARE @Rows INT
SET @Rows = 1

WHILE (@Rows > 0)
BEGIN
   
     with ToDelete(EMailNotificationID)
         as
         (
         select top 100 q.EMailNotificationID from email
         )

         delete from ToDelete


    SET @Rows = @@ROWCOUNT
END