Click here to Skip to main content
12,405,700 members (63,795 online)
Click here to Skip to main content
Add your own
alternative version

Stats

71.2K views
31 bookmarked
Posted

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 .


You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
Henry.Scott16-Jul-12 9:23
memberHenry.Scott16-Jul-12 9:23 
GeneralDynamic Parallelization without Coding Pin
Member 79040187-May-11 1:13
memberMember 79040187-May-11 1:13 
GeneralClumsy Implementation Pin
ncarey1-Feb-06 10:26
memberncarey1-Feb-06 10:26 
GeneralSuggestion: Before & After Pin
Drew Noakes30-Jan-06 22:14
memberDrew Noakes30-Jan-06 22:14 
GeneralErrors Pin
nikumi30-Jan-06 6:01
membernikumi30-Jan-06 6:01 
GeneralMore Details Pin
John Kendrick24-Jan-06 7:11
memberJohn Kendrick24-Jan-06 7:11 
GeneralRe: More Details Pin
toticow24-Jan-06 22:34
membertoticow24-Jan-06 22:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web01 | 2.8.160721.1 | Last Updated 24 Jan 2006
Article Copyright 2006 by Himanshu Kumar Sinha
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid