November 30, 2004 at 12:59 pm
Is there a way to commit transactions in an insert after say 10,000 rows are selected?
November 30, 2004 at 1:29 pm
Could you use a cursor to count rows?
Sean Wyatt
seanwyatt.com
November 30, 2004 at 1:34 pm
put it in a while loop that checks @@rowcount
use SET ROWCOUNT 10000
November 30, 2004 at 2:25 pm
could you give me a short query that shows this loop
December 1, 2004 at 7:27 am
You'll need something like this
first create a new field that flags the updated rows
then updated the rows that are not flaged as updated.
I keep the rowcount in @MyRowCount because when I do set@i =
@@rowcount is reset to 0
use documentation
alter table ObjSQL
ADD tmp tinyint null
go
Declare @i as int
Declare @MyRowCount as int
Declare @BatchSize as int
set @BatchSize = 1000
set @i = 1
SET ROWCOUNT @BatchSize
Update ObjSQL set tmp = 1 where tmp is null
set @MyRowCount = @@rowcount
while @MyRowCount = @BatchSize
begin
Update ObjSQL set tmp = 1 where tmp is null
set @MyRowCount = @@rowcount
set @i = @i + 1
end
print @i
alter table ObjSQL Drop column tmp
go
SET ROWCOUNT 0
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply