IDENTITY_INSERT ON inside of cursor does not allow inserted id

Question

I am trying to set some id's for a bunch of rows in a database where the id column is an identity.

I've created a cursor to loop through the rows and update the ids with incrementing negative numbers (-1,-2,-3 etc).

When I updated just one row turning on the IDENTITY_INSERT it worked fine but as soon as I try and use it in a cursor, it throws the following error.

Msg 8102, Level 16, State 1, Line 22 Cannot update identity column 'myRowID'.

DECLARE @MinId  INT;
SET @MinId = (SELECT MIN(myRowId) FROM myTable)-1;

DECLARE myCursor CURSOR
FOR
SELECT myRowId
FROM dbo.myTable
WHERE myRowId > 17095

OPEN myCursor 
DECLARE @myRowId INT

FETCH NEXT FROM myCursor INTO @myRowId
WHILE (@@FETCH_STATUS <> -1)
BEGIN

SET IDENTITY_INSERT dbo.myTable ON;

--UPDATE dbo.myTable
--SET myRowId = @MinId
--WHERE myRowId = @myRowId;

PRINT (N'ID: ' + CAST(@myRowId AS VARCHAR(10)) + N' NewID: ' + CAST(@MinId AS VARCHAR(4)));
SET @MinId = @MinId - 1;
FETCH NEXT FROM myCursor INTO @myRowId
END

CLOSE myCursor 
DEALLOCATE myCursor 
GO
SET IDENTITY_INSERT dbo.myTable OFF;
GO

Does anyone know what I'm doing wrong?

Answer 1

You wouldn't need a cursor for this anyway. Ignoring that they are identity columns something like this would work in a derived table then you could join onto it to update all rows in a set based way

select 0-row_number() over( order by myRowId asc) as myRowId,*
 from dbo.myTable
WHERE myRowId > 17095

This still might be a useful approach if you end up setting identity insert on then inserting them all like that then deleting ones WHERE myRowId > 17095 (in that order!) in a transaction

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRAN
SET IDENTITY_INSERT dbo.myTable ON;

INSERT INTO dbo.myTable
SELECT 0-row_number() OVER( ORDER BY myRowId ASC) AS myRowId, OtherColumns
 FROM dbo.myTable
WHERE myRowId > 17095

DELETE FROM dbo.myTable WHERE myRowId > 17095

SET IDENTITY_INSERT dbo.myTable OFF;
COMMIT