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?
017 Answers
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 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
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 --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 Try using DBCC CHECKIDENT:
DBCC CHECKIDENT ('YourTable', RESEED, 1); 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
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
0copy 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 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.
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
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 (); } I had similar problem I needed update some IDs what I did was ( i needed to Increase them by 10k ):
set identity_insert YourTable ON INSERT INTO YourTable ([ID] ,[something1] ,[something2] ,[something3]) SELECT ([ID] + 10000) ,[something1] ,[something2] ,[something3]) FROM YourTable WHERE something1 = 'needs updeted id' AND something2 = 'some other condition' set identity_insert YourTable OFF DELETE FROM YourTable WHERE ID >= 'your old ID From' AND ID <= 'Your old ID To' And that's it. Hope you understand this logic, in my case there was also PK-FK keys connection with other tables, which meant i had to update them, before i could delete from 'YourTable' original rows.
I know there is already answers to this I just wanted to leave SQL query as example,
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.
1You 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.
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.
6I did the following:
- MOVE related data into temporary storage
- UPDATE primary key/identity column value (dropping and creating constraints)
- RE-INSERT related data with new foreign key value
I wrapped my solution in a STORED PROCEDURE:
CREATE PROCEDURE [dbo].[UpdateCustomerLocationId] @oldCustomerLocationId INT, @newCustomerLocationId INT AS /* Updates CustomerLocation.CustomerLocationId @oldCustomerLocationId to @newCustomerLocationId Example: EXEC [dbo].[UpdateCustomerLocationId] @oldCustomerLocationId = 6154874, @newCustomerLocationId = 50334; */ BEGIN SET NOCOUNT ON; -- exit if @oldCustomerLocationId does not exists IF NOT EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId) BEGIN PRINT CONCAT('CustomerLocationId ''', @oldCustomerLocationId, ''' (@oldCustomerLocationId) does not exist in dbo.CustomerLocation'); RETURN 1; -- 0 = success, > 0 = failure END -- exit if @newCustomerLocationId already exists IF EXISTS (SELECT * FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @newCustomerLocationId) BEGIN PRINT CONCAT('CustomerLocationId ''', @newCustomerLocationId, ''' (@newCustomerLocationId) already exists in dbo.CustomerLocation'); RETURN 2; -- 0 = success, > 0 = failure END BEGIN TRAN; BEGIN -- MOVE related data into temporary storage IF EXISTS (SELECT * FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId) BEGIN IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL DROP TABLE #CustomerLocationData; SELECT * INTO #CustomerLocationData FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId; DELETE t FROM dbo.CustomerLocationData t WHERE t.CustomerLocationId = @oldCustomerLocationId; END END BEGIN -- UPDATE dbo.CustomerLocation -- DROP CONSTRAINTs ALTER TABLE [dbo].[CustomerLocation] DROP CONSTRAINT [UC_CustomerLocation]; -- INSERT OLD record with new CustomerLocationId SET IDENTITY_INSERT dbo.CustomerLocation ON; INSERT INTO dbo.CustomerLocation ( CustomerLocationId, CustomerId, LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId, CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer, Comments, LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary ) SELECT @newCustomerLocationId AS CustomerLocationId, CustomerId,LocationId, CustomerLocationIdent, CustomerLocationIdent2, LocationIdent, LocationName, CustomerDistrictId, CustomerLocationGUID, UpdatedOn, IssueManager, EnrollSelfMonitoring, TemperatureControlDeadlineHour, CreatedOn, OperationBegin, ActiveCustomer, Comments,LocationName2, ParentGroup, TempString1, TempString2, TempString3, TempString4, TempString5, AutoInheritFromLocation, ClassificationPrimary FROM dbo.CustomerLocation WHERE CustomerLocationId = @oldCustomerLocationId; SET IDENTITY_INSERT dbo.CustomerLocation OFF; -- DELETE OLD record DELETE cl FROM dbo.CustomerLocation cl WHERE cl.CustomerLocationId = @oldCustomerLocationId; -- ADD CONSTRAINTS ALTER TABLE [dbo].[CustomerLocation] ADD CONSTRAINT [UC_CustomerLocation] UNIQUE NONCLUSTERED ([CustomerId], [LocationId]); END BEGIN -- re-INSERT related data from temporary storage IF OBJECT_ID('tempdb..#CustomerLocationData') IS NOT NULL BEGIN SET IDENTITY_INSERT dbo.CustomerLocationData ON; INSERT INTO dbo.CustomerLocationData (Guid, CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data) SELECT Guid, @newCustomerLocationId CustomerLocationId, CustomerLocationDataTypeId, Date, Category, Data FROM #CustomerLocationData; SET IDENTITY_INSERT dbo.CustomerLocationData OFF; END END COMMIT TRAN; END This happened to me because I did a merge that was updating the ID that I was doing the Merge on.
Example that did not work (note ClownID):
MERGE ArchitectMain.dbo.BackendClowns AS TGT USING ( SELECT ClownID ,ClownName ,Description ,Active ,EmailSubject ,AddedBy ,Added FROM #temptable1 ) AS SRC(ClownID, ClownName, Description, Active, EmailSubject, AddedBy, Added) ON (TGT.ClownID = SRC.ClownID) WHEN MATCHED THEN UPDATE SET ClownID = SRC.ClownID ,ClownName = SRC.ClownName ,Description = SRC.Description ,Active = SRC.Active ,EmailSubject = SRC.EmailSubject ,AddedBy = SRC.AddedBy ,Added = SRC.Added; Example that worked (note ClownID):
MERGE ArchitectMain.dbo.BackendClowns AS TGT USING ( SELECT ClownID ,ClownName ,Description ,Active ,EmailSubject ,AddedBy ,Added FROM #temptable1 ) AS SRC(ClownID, ClownName, Description, Active, EmailSubject, AddedBy, Added) ON (TGT.ClownID = SRC.ClownID) WHEN MATCHED THEN UPDATE SET ClownName = SRC.ClownName ,Description = SRC.Description ,Active = SRC.Active ,EmailSubject = SRC.EmailSubject ,AddedBy = SRC.AddedBy ,Added = SRC.Added; 
