SQL - batched deletes

batched deletes

-- Step 1 : Declare the varaibles

use Northwind
Declare @counter int
Declare @RowsEffected int
Declare @RowsCnt int
Declare @CodeId int
Declare @OldCodeId int
Declare @Err int
SELECT @COUNTER = 1
SELECT @RowsEffected = 0
 


/*
Step 2 : Get the value of the Code , with which we need to update the existing Code . In my case I am capturing is from a table , we can always hard code it .
*/

SELECT @CodeID  = 23
SELECT @OldCodeid = 24

/*
Step 3: Start the while loop ,  if we have 100,000 records , and in each loop 5,000 records will be update , so the total number of cycle will be 100,000/5000 i.e 20
*/

WHILE ( @COUNTER > 0)
 BEGIN 
         SET ROWCOUNT 5000  

-- Note : The SET ROWCOUNT 5000   will just pick the top 5000 records */
  UPDATE Test
      SET CodeID= @CodeID                           
    WHERE Codeid = @OldCodeID                                 
            SELECT  @RowsCnt = @@ROWCOUNT ,@Err = @@error
     IF @Err <> 0   
    BEGIN   
     Print 'Problem Updating the records'                  
    END
            IF @RowsCnt = 0
               SELECT @COUNTER = 0
            ELSE

/* Increment the Counter */

            SELECT @RowsEffected =  @RowsEffected + @RowsCnt
            PRINT 'The total number of rows effected :'+convert(varchar,@RowsEffected) 

            /*delaying the Loop for 10 secs , so that Update is comepleted*/    

            WAITFOR  DELAY '00:00:10'            
 END


--Step 4 : Check if all the records are updated or not .
IF EXISTS ( SELECT CodeID
              FROM Test (NOLOCK)
             WHERE CodeID = @OldCodeid
           )
BEGIN
PRINT ('All the records are not updated , there is some  problem , Contact Devs  ')
END
BEGIN
PRINT ('All the records are  updated SUCCESSFULLY !!!!')
END
/* ------Set rowcount to default ----*/
SET ROWCOUNT 0