How to change identity column values programmatically?
I have a MS SQL 2005 database with a table
Test with column
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.
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
Read more... Read less...
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 ALL SELECT 'Bar' UNION ALL SELECT '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
CREATE SEQUENCE Seq AS INT START WITH 1 INCREMENT BY 1 CREATE TABLE Test2 ( ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY, X VARCHAR(10) ) INSERT INTO Test2(X) SELECT 'Foo' UNION ALL SELECT 'Bar' UNION ALL SELECT '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.
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.
This can be done using a temporary table.
- disable constraints (in case your id is referenced by a foreign key)
- create a temp table with the new id
- delete the table content
- copy back data from the copied table to your original table
- enable previsously disabled constraints
Let's say your
test table have two additional columns (
column3) and that there are 2 tables having foreign keys referencing
foreign_table2 (because real life issues are never simple).
alter table test nocheck constraint all; alter table foreign_table1 nocheck constraint all; alter table foreign_table2 nocheck constraint all; set identity_insert test on; select id + 1 as id, column2, column3 into test_copy from test v; delete from test; insert into test(id, column2, column3) select id, column2, column3 from test_copy alter table test check constraint all; alter table foreign_table1 check constraint all; alter table foreign_table2 check constraint all; set identity_insert test off; drop table test_copy;
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