Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Updating Records in chunks Expand / Collapse
Author
Message
Posted Monday, May 18, 2009 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 3, 2010 5:50 AM
Points: 5, Visits: 15
Hi,

I am looking for an approach wherein I can update the records in chunk like if there are 1500 records in a table to be updated so I want to update them in a set of 100 records.

Regards,
Sheetal
Post #718850
Posted Monday, May 18, 2009 1:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,219, Visits: 1,430
Is there a reason for this piecemeal update?

I can think of 2 methods that may be of use. You could use TOP, or a WHILE Loop.
Post #718857
Posted Monday, May 18, 2009 2:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 3, 2010 5:50 AM
Points: 5, Visits: 15
yes ...since I have got a batch job running every day and the records that are getting affected are in lakhs so need to implement a logic in part updation so that the system is not down for a long time ...

Do you have any sample logic ?
Post #718868
Posted Monday, May 18, 2009 3:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, August 3, 2014 3:41 PM
Points: 1,219, Visits: 1,430
something like this- assuming there is a 1 to 1 relationship tblA.pKey = tblB.updateKey

UPDATE tblA
SET tblA.col1 = tblB.col1
FROM tblB
WHERE tblA.pKey IN (SELECT TOP (100) tblB.updateKey
WHERE ... update not applied condition ...)

OR

DECLARE @COUNT INT
SET @COUNT = 0

WHILE (@COUNT < 1500 )
BEGIN

WITH NumberedRows AS (
SELECT ROW_NUMBER() OVER(Order By tblB.updateKey) AS RN, *
FROM tblB )
UPDATE tblA
SET tblA.col1 = NumberedRows.col1
FROM NumberedRows
WHERE NumberedRows.RN BETWEEN ( @COUNT AND (@COUNT + 100) )
AND tblA.pKey = tblB.updateKey
SET @COUNT = @COUNT + 100;

END
Post #718887
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse