November 18, 2010 at 1:13 pm
I have a update script
update membership
set effdat=enrolldat
There problem is that I need to update 60 million row of data.
How can I do to make the transcation commit every 10000 row.
Thanks,
Qjlee
November 18, 2010 at 2:36 pm
would need the actual talbe def and some sample date. Nonetheesee there are several ways. If you're not concerned w using transactions then u can use and update statement join the table being ubdated join to the same table w/ a select top 10000 in there.
If you must use transactions then u can either loop though the set using a cursor or what i call is a manual cursor which is loading the recs into a temp table and loping through them w/ a rocessed flag or an id of sorts to keep you place. Again w/o table defs and data your not going to get very far on this site.
November 19, 2010 at 5:12 am
hard to say with out sample data and no table structure
a very simple way is
top 10000 control with a WHERE effdat <> enrolldat
If you give me some more info I can write the statement
I should be able to do this without using cursor, temp tables , or flags
Scott
November 19, 2010 at 5:23 am
QQ-485619 (11/18/2010)
How can I do to make the transcation commit every 10000 row.
There is a complete explanation and script in the following SSC article by Lynn Pettis:
http://qa.sqlservercentral.com/articles/67898/
The article covers deleting a large number of records efficiently, but the principle is exactly the same for update.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 19, 2010 at 5:46 am
Paul White NZ (11/19/2010)
QQ-485619 (11/18/2010)
How can I do to make the transcation commit every 10000 row.There is a complete explanation and script in the following SSC article by Lynn Pettis:
http://qa.sqlservercentral.com/articles/67898/
The article covers deleting a large number of records efficiently, but the principle is exactly the same for update.
This is the method I us I have a SPROC that I just past the statement and tran ct one thing you need to remember that the where clause must not create a loop something like 1=1 will only update the top 10000 records over and over
November 19, 2010 at 6:20 am
shump66 (11/19/2010)
This is the method I us I have a SPROC that I just past the statement and tran ct one thing you need to remember that the where clause must not create a loop something like 1=1 will only update the top 10000 records over and over
Yes, I thought that was what you meant - it reminded me of Lynn's article.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply