How to use an Update Cursor on MS SQL Server.
Requirements: If the update-table does not have a Primary Key, the cursor is read-only. So I advise to create at least a temporary Primary Key, and delete it afterwards.
Usage:
DECLARE curs as CURSOR
FOR SELECT * FROM t_image
FOR UPDATE
OPEN curs
FETCH curs INTO @lastValue
WHILE (@@FETCH_STATUS = 0) BEGIN
UPDATE [t_image]
SET [CALC_VALUE] = @lastValue
WHERE current of curs
FETCH curs INTO @lastValue
END
CLOSE calc
DEALLOCATE calc
if you replace “WHERE current of curs” by any “WHERE x = y” statement, it would decrease performance dramtically.
During a test with 80000 records, execution time without update cursor takes 20 minutes. Using an update cursor speeds up to a total time of 20 seconds!