IDENTITY_INSERT ON inside of cursor does not allow inserted id
- by Mac
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?
