CommitCount Command
-
Is there a "CommitCount" command in T-SQL that instructs a query to commit every 'n' records as specified in the CommitCount clause?
Something like...
Update tablename
Set field1 = value1
Where field2 = value2
CommitCount 10000
-
No and from your query I don't think SET @@ROWCOUNT will help either.
-
the best/closest one can do is to simulate this using a WHILE Loop
set rowcount 10000
WHILE @@ROWCOUNT > 0
begin
update x
set y = z
where a = b
end
set rowcount 0
-
Sorry about the SET @@ROWCOUNT, brain was on automatic pilot.
Anyway with regards to previous you will do nothing more than create a continual loop on the first bits of data becuase of the fact your where clause is not going to be altered by the update.
So when you say
set y = z
where a = b
Because a will always be b for the same records SET ROWCOUNT will update a set number of records but those same records will be updated over and over.
But since y = z when done this change should fix.
set y = z
where a = b AND y != z
By doing that you will not update rows that have already been handled.
-
I was aware my suggestion wasn't perfect....I was just trying to give a flavour of a solution!
...too busy (careless) to get it right....
any way that would be too easy for the poster.....I prefer to bring them close to the answer so that they can learn from completing the final steps themselves.
-
With this solution, it appears that I would have to call this segment of code over and over until @@rowcount = 0.
In other words, I would have to put just this update statement (along with the suggested set rowcount 10000 and WHILE @@rowcount > 0 statements) in a stored proc and then call this stored proc from within another loop and loop through until @@rowcount returns 0 from within the calling loop.
Is this correct?
-
How about:
while exists(select * from tablename where field2 = value2 and field1 <> value1)
begin
set rowcount 10000
Update tablename
Set field1 = value1
Where field2 = value2 and field1 <> value1
-- waitfor delay '00:00:01' -- want to give other processes some access?
end
-
no you just call the SP with the while loop once......
the while construct will keep updating batches of 10000 records until there are no more batches left....
and then it will move on to the next task.
try it on a small sample...(maybe add a (test) print statement inside the loop to show you the progress)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply