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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy