Deleting rows in a table a chunk at a time

Question

I have a single-column table Ids, which whose column ID is of type uniqueidentifier. I have another table MyTable which has an ID column as well as many other columns. I would like to delete rows from MyTable 1000 at a time, where the ID from MyTable matches an ID in Ids.

WHILE 1 = 1 BEGIN
    DELETE t FROM (SELECT TOP 1000 ID FROM Ids) d INNER JOIN MyTable t ON d.ID = t.ID;
    IF @@ROWCOUNT < 1 BREAK;
    WAITFOR DELAY @sleeptime; -- some time to be determined later
END

This doesn't seem to work though. What should the statement actually be?

Answer 1

Try this:

DECLARE @BatchSize INT
SET @BatchSize = 100000

WHILE @BatchSize <> 0
BEGIN 
   DELETE TOP (@BatchSize) t
    FROM [MyTable] t
    INNER JOIN [Ids] d ON d.ID=t.ID
    WHERE ????
   SET @BatchSize = @@rowcount 
END

Has the benefit that the only variable you need to create is the size, as it uses it for the WHILE loop check. When the delete gets below 100000, it will set the variable to that number, on the next pass there will be nothing to delete and the rowcount will be 0... and so you exit. Clean, simple, and easy to understand. Never use a CURSOR when WHILE will do the trick!