How to change identity column values programmatically?

tomaszs_38940 0
Viewed 324187

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.

sql-server-2005-express identity sql-server-2005 tsql sql-server
13 Answers

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
user_1089770

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.

user_2450

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.

miles_38980

DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999) you can change any identity column number with this command,and also you can start that field number from every number you want.for example in my command i ask to start from 1000 (999+1) hope that it would be enough...good luck

user_13275560

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!

user_176320

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

user_574750

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.

Robin_Bennett_277940
Related