Help with Incremental update in sql server
-
HI,
I am trying to run an update statement on a table that has 13 million records
how do i do an incremental update, that is updating 100000 records at a time?
I am trying to set rowcount to 100000 and do a commit after the update is done.
any help is appreciated thanks..
-
Without a bit more info about the table and the reason for the update, best I can say is...
SET ROWCOUNT 100000
SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
.... do your update
END
SET ROWCOUNT 0
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
and make the
... do your updates ...
part distinctive !
(so you don't update the same 1000 rows every time and again...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
FWIW:
Microsoft encourages the use of the TOP clause in SQL 2005+ over the SET ROWCOUNT statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Hi, id like to be able to update 100000 rows at a time and commit the transaction. Basically what i am trying to do is break a long running update statement into smaller chunks for performance reasons.
-
shobana.rajaram (9/18/2008)
Hi, id like to be able to update 100000 rows at a time and commit the transaction. Basically what i am trying to do is break a long running update statement into smaller chunks for performance reasons.If you use the above approach, each UPDATE statement will be in its own transaction and committed as such.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply