How to change identity column values programmatically?

Question

I have a MS SQL 2005 database with a table Test with column ID. ID is a identity column. I have rows in this table and all of them have their corresponding ID autoincremented value.

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

ID = ID + 1

But when I do this I get error:

Cannot update identity column 'ID'.

I've tried with:

ALTER TABLE Test NOCHECK CONSTRAINT ALL 
set identity_insert ID ON

But this does not solve this 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 acomplish this task that is required by my project.

Answer 1

Through the UI in SQL Server 2005 manager, change the column remove the autonumber (identity) property of the column (select the table by right clicking on it and choose "Design").

Then run your query

UPDATE table SET Id = Id + 1

Then go and add the autonumber property back to the column.

Answer 2

If the column is not a PK you could always create a NEW column in the table with the incremented numbers, drop the original and then alter the new one to be the old.

curious as to why you might need to do this... most I've ever had to futz with Identity columns was to backfill numbers and I just ended up using DBCC CHECKIDENT ( tablename,RESEED,newnextnumber)

good luck!

Answer 3

Identity modifying may fail depending on a number of factors, mainly revolving around the objects/relationships linked to the id column. It seems like db design is as issue here as id's should rarely if ever change (i'm sure you have your reasons and are cascasding the changes). If you really need to change id's from time to time, I'd suggest either creating a new dummy id column that isn't the primary key/autonumber that you can manage yourself and generate from the current values. Alternately, Chrisotphers idea above would be my other suggestion if you're having issues with allowing identity insert.

Good luck

PS it's not failing because the sequential order it's running in is trying to update a value in the list to an item that already exists in the list of ids? clutching at straws, perhaps add the number of rows+1, then if that works subtract the number of rows :-S

Answer 4

Firstly the setting of IDENTITY_INSERT on or off for that matter will not work for what you require (it is used for inserting new values, such as plugging gaps).

Doing the operation through the GUI just creates a temporary table, copies all the data across to a new table without an identity field, and renames the table.

Answer 5

As Miles D and MichaelPryor have already pointed out, you will have to create a new temp table, insert data there from the existing table (incrementing the value, maybe), dropping and recreating / truncating / deleting the original table and recreating from the temp table. All of this could not make sense if the IDENTITY field is also a PRIMARY key, referenced by external keys of some other table(s).

Answer 6

If you need to change the IDs occasionally, it's probably best not to use an identity column. In the past we've implemented autonumber fields manually using a 'Counters' table that tracks the next ID for each table. IIRC we did this because identity columns were causing database corruption in SQL2000 but being able to change IDs was occasionally useful for testing.

Answer 7

You need to

set identity_insert YourTable ON

Then delete your row and reinsert it with different identity.