October 16, 2006 at 8:54 am
Hi All,
I am trying to write a code that would basically perform updates in 100's rather than in one transaction:
Create table test_bulk_update
(
Value varchar(20),
Updated varchar(1)
)
declare @start int
declare @end int
set @start = 1
set @end = 2000
while @start <= @end
begin
insert into test_bulk_update (Value)
Select 'Rownumber = ' + convert(varchar(10),@start)
set @start = @start + 1
End
Now the idea is to do the updates in 100's., so there would be 20 seperate update transactions.
The code below would do the updates in 1 go, but I dont want to do it in one go.
Update test_bulk_update
set updated = 'Y'
October 16, 2006 at 9:12 am
Use @@ROWCOUNT, something like this
SET ROWCOUNT 100
DECLARE @rowcount int
SET @rowcount = 1
WHILE @rowcount > 0
BEGIN
UPDATE test_bulk_update
SET updated = 'Y'
WHERE updated IS NULL
SET @rowcount = @@ROWCOUNT
END
SET ROWCOUNT 0
Note the line in red, otherwise you will have infinate loop
Far away is close at hand in the images of elsewhere.
Anon.
October 17, 2006 at 7:34 am
This will work too.
declare
@start int
declare
@end int
set
@start=1
set
@end=100
while
@start<@end
BEGIN
begin transaction test
update test_bulk_update set updated='Y' where updated is null and Value in
(select top 100 Value from test_bulk_update where updated is NULL)
commit transaction test
set @start = @start + 100
END
;
October 17, 2006 at 9:45 am
Just update the @end to 2000 in your case.
October 18, 2006 at 2:58 am
You could also try this script, it loops until the total count of updated records is greater than the number of records retrieved.
Declare @TotRec as Numeric(18), @Cnt as Numeric(18)
Select @TotRec = Count(*) from Test_Bulk_Update Where Updated Is Null
Set @Cnt = 0
While @Cnt < @TotRec
Begin
Set Rowcount 100
Update Test_Bulk_Update Set Updated = 'Y' Where Updated Is Null
Set @Cnt = @Cnt + 100
End
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