Updating Records in chunks

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Updating Records in chunks

Updating Records in chunks

Rate Topic

Display Mode

Topic Options

Author

Message

sheetaljain22

Posted Monday, May 18, 2009 1:44 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 03, 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

Tom Brown

Posted Monday, May 18, 2009 1:59 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 24, 2013 9:02 AM
Points: 1,196, Visits: 1,320

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

sheetaljain22

Posted Monday, May 18, 2009 2:21 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 03, 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

Tom Brown

Posted Monday, May 18, 2009 3:04 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 24, 2013 9:02 AM
Points: 1,196, Visits: 1,320

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 »

Permissions