Menu
In a table, I have an ID column, that is an Identity int.
How can I make it so that the next row inserted will get identity 10000 (I believe this is called the identity seed) - without dropping and recreating the table?
I need to do this because of a uniqueness issue with an external service, the app accesses.
870
Changing the identity increment value Unfortunately there's no easy way to change the increment value of an identity column. The only way to do so is to drop the identity column and add a new column with the new increment value.
Steps for updating existing identity column valuesRemove all the foreign key constraints referencing the identity column. Copy all the records from the identity table and insert it to a staging table. Now, switch ON IDENTITY_INSERT for the identity table to allow inserting values to the identity Column.
To manually insert a new value into the Id column, we first must set the IDENTITY_INSERT flag ON as follows: SET IDENTITY_INSERT Students ON; To set the IDENTIT_INSERT flag ON we need to use the SET statement followed by the flag name and the name of the table.
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)
DBCC CHECKIDENT (yourtable, reseed, 9999)
This will make the next entry 10000.
186
This should do it:
DBCC CHECKIDENT (MyTableName, RESEED, 9999)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
