How to change identity column values programmatically?

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

IDENTITY column values are immutable.

However it is possible to switch the table metadata to remove the IDENTITY property, do the update, then switch back.

Assuming the following structure

CREATE TABLE Test(ID INT IDENTITY(1,1) PRIMARY KEY,X VARCHAR(10))INSERT INTO Test OUTPUT INSERTED.*SELECT 'Foo' UNION ALLSELECT 'Bar' UNION ALLSELECT 'Baz'

Then you can do

/*Define table with same structure but no IDENTITY*/CREATE TABLE Temp(ID INT PRIMARY KEY,X VARCHAR(10))/*Switch table metadata to new structure*/ALTER TABLE Test SWITCH TO Temp;/*Do the update*/UPDATE Temp SET ID = ID + 1;/*Switch table metadata back*/ALTER TABLE Temp SWITCH TO Test;/*ID values have been updated*/SELECT *FROM Test/*Safety check in case error in preceding step*/IF NOT EXISTS(SELECT * FROM Temp)    DROP TABLE Temp /*Drop obsolete table*/

In SQL Server 2012 it is possible to have an auto incrementing column that can also be updated more straightforwardly with SEQUENCES

CREATE SEQUENCE Seq    AS INT    START WITH 1    INCREMENT BY 1CREATE TABLE Test2(ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY,X VARCHAR(10))INSERT INTO Test2(X)SELECT 'Foo' UNION ALLSELECT 'Bar' UNION ALLSELECT 'Baz'UPDATE Test2 SET ID+=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.