May 18, 2009 at 1:44 am
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
May 18, 2009 at 1:59 am
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.
May 18, 2009 at 2:21 am
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 ?
May 18, 2009 at 3:04 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply