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 Answer
You need to
Then delete your row and reinsert it with different identity.
Once you have done the insert don't forget to turn identity_insert off