IDENTITY_INSERT No inserted identifier allowed inside cursor
I am trying to set some id for a group of rows in a database where id column is id.
I created a cursor to scroll through lines and update IDs with negative appended numbers (-1, -2, -3, etc.).
When I only updated one row that included IDENTITY_INSERT it worked fine, but as soon as I try to use it in the cursor it throws the following error.
Msg 8102, Level 16, State 1, Line 22 Unable to 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 am doing wrong?
a source to share
You don't need a cursor anyway. Ignoring that they are identity columns, something like this will work in a view, then you can join to update all rows based on the set.
select 0-row_number() over( order by myRowId asc) as myRowId,*
from dbo.myTable
WHERE myRowId > 17095
This can be a useful approach if you end up setting the identity insert and then insert them all the same way and then remove WHERE myRowId> 17095 (in that order!) In the 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
a source to share