sql-serversql-server-2005t-sqlidentitysql-server-2005-express

How to change identity column values programmatically?

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.

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