set identity_insert Geo Country on go insert into Geo Country (all columns including IDentity column) select 18, (all columns except IDentity column) from Geo Country where Country ID = 250 -- Delete will only work if no referencing FK's delete Geo Country where Country ID = 250 set identity_insert Geo Country off go You cannot update the Identity Column in SQL Server.You have to delete the original record, then Insert the record with the Identity value because there is no support for updating an identity value.set Identity_Insert [Column Name] On Insert identity and additional information previously stored in that record set Identity_Insert [Column Name] Off 1 IMO this is the correct answer.When posed with the question "How do I update the identity value" it is an important detail that you cannot in fact UPDATE the value but must DELETE and re-INSERT.I have a table filled with data and one of the columns - Tracking Number - is an integer value.I have gotten a request to change it to auto-increment and to have it start the identity seed at 1000000.
The table specified must contain an identity column.
There is no straightforward T-SQL like ALTER TABLE… Let’s first see what SSMS does in backend when you add Identity property on an existing column in any table. All you have to do is just select Yes from the drop down list and you are done!
or MODIFY COLUMN to add an Identity Property to an existing column in a table. Now, let’s create an example table for better understanding. But before moving further let’s see what T-SQL SQL Server is using to make this change.
If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values.
If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.