Sql-server – How to change identity column values programmatically

identitysql-serversql-server-2005sql-server-2005-expresstsql

I have a MS SQL 2005 database with a table Test with column ID. ID is an identity column.

I have rows in this table and all of them have their corresponding ID auto incremented value.

Now I would like to change every ID in this table like this:

ID = ID + 1

But when I do this I get an error:

Cannot update identity column 'ID'.

I've tried this:

    ALTER TABLE Test NOCHECK CONSTRAINT ALL 
    set identity_insert ID ON

But this does not solve the problem.

I need to have identity set to this column, but I need to change values as well from time to time. So my question is how to accomplish this task.

Best Solution

You need to

set identity_insert YourTable ON

Then delete your row and reinsert it with different identity.

Once you have done the insert don't forget to turn identity_insert off

set identity_insert YourTable OFF