Batch Update on a very huge table

Those of us who work in the Production / Live DB , know the pain , of firing a update query on a huge table . This is very problematic as this will lead to the following

• Lock escalation : The entire table will be locked by your application , so other users /  application will not be able to perform the DDL on this table
• The TempDB will grow huge
• A update lock on a table will stop other users , for making changes in the table

I have written a below snippet which will help us fight the above issues

-- Step 1 : Declare the varaibles

use DBNAME<BR>Declare @counter int <BR>Declare @RowsEffected int <BR>Declare @RowsCnt int <BR>Declare @CodeId int <BR>Declare @Err int<BR>SELECT @COUNTER = 1<BR>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 = CodeID FROM CODE WHERE XXXX ='YYYY'


/*
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)<BR> BEGIN <BR> SET ROWCOUNT 5000

-- Note : The SET ROWCOUNT 5000   will just pick the top 5000 records */

   
      /*UPDATING TABLE */
  UPDATE Table<BR> SET CodeID= @CodeID <BR> WHERE Codeid = @OldCode <BR> SELECT @RowsCnt = @@ROWCOUNT ,@Err = @@error <BR> IF @Err <> 0 <BR> BEGIN <BR> Print 'Problem Updating the records' <BR> END <BR> IF @RowsCnt = 0<BR> SELECT @COUNTER = 0 <BR> ELSE <BR>
/* Increment the Counter */

  SELECT @RowsEffected = @RowsEffected + @RowsCnt <BR> PRINT 'The total number of rows effected :'+convert(varchar,@RowsEffected) <BR>
            /*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<BR> FROM Table (NOLOCK)<BR> WHERE CodeID = @OldCodeid<BR> )<BR>BEGIN <BR>PRINT ('All the records are not updated , there is some problem , Contact Devs ')<BR>END<BR>BEGIN <BR>PRINT ('All the records are updated SUCCESSFULLY !!!!')<BR>END <BR>/* ------Set rowcount to default ----*/
SET ROWCOUNT 0