[sql-server] How to change identity column values programmatically?

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.

This can be done using a temporary table.

The idea

• 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

SQL Queries

Let's say your test table have two additional columns (column2 and column3) and that there are 2 tables having foreign keys referencing test called foreign_table1 and 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;

That's it.

First save all IDs and alter them programmatically to the values you wan't, then remove them from database and then insert them again using something similar:

use [Name.Database]
go
set identity_insert [Test] ON
insert into [dbo].[Test]
           ([Id])
     VALUES
           (2)
set identity_insert [Test] OFF

For bulk insert use:

use [Name.Database]
go
set identity_insert [Test] ON
BULK INSERT [Test]
FROM 'C:\Users\Oscar\file.csv'
WITH (FIELDTERMINATOR = ';',
      ROWTERMINATOR = '\n',
      KEEPIDENTITY)
set identity_insert [Test] OFF

Sample data from file.csv:

2;
3;
4;
5;
6;

If you don't set identity_insert to off you will get the following error:

Cannot insert explicit value for identity column in table 'Test' when IDENTITY_INSERT is set to OFF.

Very nice question, first we need to on the IDENTITY_INSERT for the specific table, after that run the insert query (Must specify the column name).

Note: After edit the the identity column, don't forget to off the IDENTITY_INSERT. If you not done, you cannot able to Edit the identity column for any other table.

SET IDENTITY_INSERT Emp_tb_gb_Menu ON
     INSERT Emp_tb_gb_Menu(MenuID) VALUES (68)
SET IDENTITY_INSERT Emp_tb_gb_Menu OFF

http://allinworld99.blogspot.com/2016/07/how-to-edit-identity-field-in-sql.html

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.

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!

You can insert new rows with modified values and then delete old rows. Following example change ID to be same as foreign key PersonId

SET IDENTITY_INSERT [PersonApiLogin] ON

INSERT INTO [PersonApiLogin](
       [Id]
      ,[PersonId]
      ,[ApiId]
      ,[Hash]
      ,[Password]
      ,[SoftwareKey]
      ,[LoggedIn]
      ,[LastAccess])
SELECT [PersonId]
      ,[PersonId]
      ,[ApiId]
      ,[Hash]
      ,[Password]
      ,[SoftwareKey]
      ,[LoggedIn]
      ,[LastAccess]
FROM [db304].[dbo].[PersonApiLogin]
GO

DELETE FROM [PersonApiLogin]
WHERE [PersonId] <> ID
GO
SET IDENTITY_INSERT [PersonApiLogin] OFF
GO

I saw a good article which helped me out at the last moment .. I was trying to insert few rows in a table which had identity column but did it wrongly and have to delete back. Once I deleted the rows then my identity column got changed . I was trying to find an way to update the column which was inserted but - no luck. So, while searching on google found a link ..

1. Deleted the columns which was wrongly inserted
2. Use force insert using identity on/off (explained below)

http://beyondrelational.com/modules/2/blogs/28/posts/10337/sql-server-how-do-i-insert-an-explicit-value-into-an-identity-column-how-do-i-update-the-value-of-an.aspx

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

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.

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.

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 SEQUENCES

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

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