Click here to Skip to main content
Click here to Skip to main content
Go to top

Batch Update on a very huge table

, 24 Jan 2006
Rate this:
Please Sign up or sign in to vote.
Updating a huge table in Production , without locking the whole 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
Declare @counter int
Declare @RowsEffected int
Declare @RowsCnt int
Declare @CodeId 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 = 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)
BEGIN
SET ROWCOUNT 5000


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

   
      /*UPDATING TABLE */
  UPDATE Table
SET CodeID= @CodeID
WHERE Codeid = @OldCode
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 Table (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

 

 

 

 

 

 


 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Himanshu Kumar Sinha
Web Developer
United States United States
He is working with reputed IT company in Hyderabad India .
 
He has got an total IT expereince of 4 yrs . I have been working with VB , .NET & SQL SERVER 2000 & Yukon .
 
he has done his B.Tech In Computer Science & Engg from North Eastern Regional Institute Of Science & Technolgy ( NERIST ) Arunachal Pradesh , Indian .
 
He enjoy's working on the latest technology , driving & cooking .
 

Comments and Discussions

 
GeneralMy vote of 1 PinmemberHenry.Scott16-Jul-12 9:23 
GeneralDynamic Parallelization without Coding PinmemberMember 79040187-May-11 1:13 
GeneralClumsy Implementation Pinmemberncarey1-Feb-06 10:26 
GeneralSuggestion: Before & After PinmemberDrew Noakes30-Jan-06 22:14 
GeneralErrors Pinmembernikumi30-Jan-06 6:01 
GeneralMore Details PinmemberJohn Kendrick24-Jan-06 7:11 
GeneralRe: More Details Pinmembertoticow24-Jan-06 22:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 24 Jan 2006
Article Copyright 2006 by Himanshu Kumar Sinha
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid