How to change identity column values programmatically?

sql-server sql-server-2005 tsql identity sql-server-2005-express
Tomasz Smykowski picture Tomasz Smykowski · Apr 15, 2009 · Viewed 316.8k times · Source

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.

Answer

A-K picture A-K · Jul 27, 2009

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More