How to change identity column values programmatically

Question

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

User · Answer

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.

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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       Deleted the columns which was wrongly inserted  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

User · Answer

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   lt  gt  ID GO SET IDENTITY INSERT  PersonApiLogin  OFF GO

User · Answer

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 · Answer

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 · Answer

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 · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to identity

Examples related to sql-server-2005-express