How to update Identity Column in SQL Server

Question

I have SQL Server database and I want to change the identity column because it started with a big number 10010 and it s related with another table  now I have 200 records and I want to fix this issue before the records increases    What s the best way to change or reset this column

User · Answer

You can create a new table using the following code.

SELECT IDENTITY (int, 1, 1) AS id, column1, column2
INTO dbo.NewTable
FROM dbo.OldTable

Then delete the old db, and rename the new db to the old db's name. Note: that column1 and column2 represent all the columns in your old table that you want to keep in your new table.

User · Answer

copy your table to a new table without identity column       select columns into newtable from yourtable   add an identity column to newtable with new seed and make it as a primary key      ALTER TABLE tableName ADD id MEDIUMINT NOT NULL AUTO INCREMENT KEY

User · Answer

If you specifically need to change the primary key value to a different number  ex 123 - gt  1123     The identity property blocks changing a PK value  Set Identity insert isn t going to work  Doing an Insert Delete is not advisable if you have cascading deletes  unless you turn off referential integrity checking   EDIT  Newer versions of SQL don t allow changing the syscolumns entity  so part of my solution has to be done the hard way   Refer to this SO on how to remove Identity from a primary key instead  Remove Identity from a column in a table This script will turn off identity on a PK                           sp configure  allow update   1 go reconfigure with override go   update syscolumns set colstat   0 --turn off bit 1 which indicates identity column where id   object id  table name   and name    column name  go   exec sp configure  allow update   0 go reconfigure with override go                           Next  you can set the relationships so they ll update the foreign key references   Or else you need to turn off relationship enforcement   This SO link shows how  How can foreign key constraints be temporarily disabled using T-SQL  Now  you can do your updates   I wrote a short script to write all my update SQL based on the same column name  in my case  I needed to increase the CaseID by 1 000 000  select   update    c table name    SET    Column Name       Column Name    1000000  from Information Schema Columns as c JOIN Information Schema Tables as t ON t table Name c table name and t Table Schema c table schema and t table type  BASE TABLE  where Column Name like  CaseID  order by Ordinal position  Lastly  re-enable referential integrity and then re-enable the Identity column on the primary key  Note  I see some folks on these questions ask WHY   In my case  I have to merge data from a second production instance into a master DB so I can shut down the second instance   I just need all the PK FKs of operations data to not collide   Meta-data FKs are identical

User · Answer

Complete solution for C  programmers using command builder  First of all  you have to know this facts    In any case  you cannot modify an identity column  so you have to delete the row and re-add with new identity  You cannot remove the identity property from the column  you would have to remove to column  The custom command builder from  net always skips the identity column  so you cannot use it for this purpose    So  once knowing that  what you have to do is  Either program your own SQL Insert statement  or program you own insert command builder  Or use this one that I be programmed for you  Given a DataTable  generates the SQL Insert script   public static string BuildInsertSQLText   DataTable table         StringBuilder sql   new StringBuilder 1000 5000000       StringBuilder values   new StringBuilder    VALUES           bool bFirst   true      bool bIdentity   false      string identityType   null       foreach DataRow myRow in table Rows                 sql Append    r nINSERT INTO     table TableName                    foreach   DataColumn column in table Columns                         if   column AutoIncrement                                 bIdentity   true                   switch   column DataType Name                                         case  Int16                           identityType    smallint                           break                      case  SByte                           identityType    tinyint                           break                      case  Int64                           identityType    bigint                           break                      case  Decimal                           identityType    decimal                           break                      default                          identityType    int                           break                                              else                               if   bFirst                       bFirst   false                  else                                       sql Append                               values Append                                             sql Append                        sql Append   column ColumnName                    sql Append                           values Append  myRow column ColumnName  ToString                       if  myRow column ColumnName  ToString       True                       values Append  1                    else if  myRow column ColumnName  ToString       False                       values Append  0                    else if myRow column ColumnName     System DBNull Value                          values Append   NULL                    else if column DataType ToString   Equals  System String                                          values Append     myRow column ColumnName  ToString                                           else                     values Append  myRow column ColumnName  ToString                           values Append  column DataType ToString                                      sql Append                   sql Append   values ToString               sql Append                   if   bIdentity                         sql Append      SELECT CAST scope identity   AS                  sql Append   identityType                sql Append                            bFirst   true          sql Append               values   new StringBuilder    VALUES               fin foreach     return sql ToString

User · Answer

I have solved this problem firstly using DBCC and then using insert  For example if your table is   Firstly set new current ID Value on the table as NEW RESEED VALUE   MyTable     IDCol    colA    colB        DBCC CHECKIDENT  MyTable   RESEED  NEW RESEED VALUE    then you can use       insert into MyTable  colA  ColB  select colA  colB from MyTable   This would duplicate all your records but using new IDCol value starting as NEW RESEED VALUE  You can then remove higher ID Value duplicate rows once your have removed moved their foreign key references  if any

User · Answer

DBCC CHECKIDENT table name  RESEED  value    table name   give the table you want to reset value   value initial value to be zero to start identity column with 1

User · Answer

Try using DBCC CHECKIDENT   DBCC CHECKIDENT   YourTable   RESEED  1

User · Answer

ALTER TABLE tablename add newcolumn int update tablename set newcolumn existingcolumnname ALTER TABLE tablename DROP COLUMN existingcolumnname  EXEC sp RENAME  tablename oldcolumn     newcolumnname    COLUMN  update tablename set newcolumnname value where condition   However above code works only if there is no primary-foreign key relation

User · Answer

SET IDENTITY INSERT dbo TableName ON INSERT INTO dbo TableName        TableId  ColumnName1  ColumnName2  ColumnName3   VALUES       TableId Value  ColumnName1 Value  ColumnName2 Value  ColumnName3 Value    SET IDENTITY INSERT dbo TableName OFF   When using Identity Insert don t forget to include the column names because sql will not allow you to insert without specifying them

User · Answer

You can also use SET IDENTITY INSERT to allow you to insert values into an identity column   Example   SET IDENTITY INSERT dbo Tool ON GO   And then you can insert into an identity column the values you need

User · Answer

If got your question right you want to do something like   update table set identity column name   some value   Let me tell you  it is not an easy process and it is not advisable to use it  as there may be some foreign key associated on it    But here are steps to do it  Please take a back-up of table  Step 1- Select design view of the table    Step 2- Turn off the identity column    Now you can use the update query   Now redo the step 1 and step 2 and Turn on the identity column  Reference

User · Answer

You can not update identity column  SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement   Although there are some alternatives to achieve a similar kind of requirement   When Identity column value needs to be updated for new records  Use DBCC CHECKIDENT which checks the current identity value for the table and if it s needed  changes the identity value  DBCC CHECKIDENT  tableName   RESEED  NEW RESEED VALUE    When Identity column value needs to be updated for existing records  Use IDENTITY INSERT which allows explicit values to be inserted into the identity column of a table  SET IDENTITY INSERT YourTable  ON OFF   Example  -- Set Identity insert on so that value can be inserted into this column SET IDENTITY INSERT YourTable ON GO -- Insert the record which you want to update with new value in the identity column INSERT INTO YourTable IdentityCol  otherCol  VALUES 13  myValue   GO -- Delete the old row of which you have inserted a copy  above   make sure about FK s  DELETE FROM YourTable WHERE ID 3 GO --Now set the idenetity insert OFF to back to the previous track SET IDENTITY INSERT YourTable OFF

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

--before running this make sure Foreign key constraints have been removed that reference the ID    --set table to allow identity to be inserted SET IDENTITY INSERT yourTable ON  GO --insert everything into a temp table SELECT    INTO  tmpYourTable FROM yourTable  --clear your table DELETE FROM yourTable --insert back all the values with the updated ID column INSERT INTO yourTable  IDCol  OtherCols  SELECT ID 1 as updatedID --put any other update logic to the ID here   OtherCols FROM  tmpYourTable --drop the temp table DROP TABLE  tmpYourTable --put identity back to normal SET IDENTITY INSERT yourTable OFF  GO

[sql] How to update Identity Column in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008