Click here to Skip to main content
13,052,022 members (63,491 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 24 Jan 2006

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<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 .


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


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

  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'         

--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 ----*/









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


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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170713.1 | Last Updated 24 Jan 2006
Article Copyright 2006 by Himanshu Kumar Sinha
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid