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

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?

This question is related to sql sql-server tsql sql-server-2008

The answer is


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

enter image description here

Step 2- Turn off the identity column

enter image description here

Now you can use the update query.

Now redo the step 1 and step 2 and Turn on the identity column

Reference


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

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.


Try using DBCC CHECKIDENT:

DBCC CHECKIDENT ('YourTable', RESEED, 1);

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.


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

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 ();
}

If you specifically need to change the primary key value to a different number (ex 123 -> 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.


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


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

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


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.


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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to sql-server-2008

Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object How to Use Multiple Columns in Partition By And Ensure No Duplicate Row is Returned SQL Server : How to test if a string has only digit characters Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query Get last 30 day records from today date in SQL Server How to subtract 30 days from the current date using SQL Server Calculate time difference in minutes in SQL Server SQL Connection Error: System.Data.SqlClient.SqlException (0x80131904) SQL Server Service not available in service list after installation of SQL Server Management Studio How to delete large data of table in SQL without log?