WHILE Loop to break up a larger update/delete job

Using a WHILE loop and TOP predicate to break an UPDATE job into smaller steps.

I use this all the time for DELETEs and UPDATEs so I thought it about time I put it here...

This approach avoids table locks by breaking down the task into trasactional parts...

DECLARE @Counter bigint
DECLARE @CounterText varchar (100)
SET @Counter = 0

WHILE  (SELECT COUNT(*) FROM dbo.tableToUpdate (NOLOCK) WHERE [_processdatetime] IS NULL) > 0 
BEGIN

SET @Counter= @Counter + 1
SET @CounterText = + CONVERT (CHAR (20), @counter, 8)
RAISERROR (@CounterText , 10, 1) WITH NOWAIT 

CHECKPOINT

UPDATE TOP (1000) dbo.tableToUpdate
  SET [_processdatetime] =  GETDATE()
    FROM dbo.tableToUpdate WHERE [_processdatetime] IS NULL
END
DECLARE @Counter bigint
DECLARE @CounterText varchar (100)
SET @Counter = 0

WHILE(1=1)
BEGIN

SET @Counter= @Counter + 1
SET @CounterText = + CONVERT (CHAR (20), @counter, 8)
RAISERROR (@CounterText , 10, 1) WITH NOWAIT 
CHECKPOINT
DELETE TOP(5000) FROM myTable WHERE dateField < ’20100318′
IF @@ROWCOUNT < 1 BREAK
END

Link : SQL Tips - Quick Delete for huge tables