how to update primary key which is auto inrement in sql

Question

how to update primary key which is auto inrement in ms-sql

Suraj Kumar · Answer

You have to run, please read the documentation https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15 DBCC CHECKIDENT( 'tableName' , RESEED, 1200)

amit · Answer

when run this in ms-sql query DBCC CHECKIDENT( 'tableName' , RESEED, NEW_RESEED_VALUE) DBCC CHECKIDENT('EnrollmentForm', 10158, 99999) Msg 2560, Level 16, State 9, Line 1 Parameter 2 is incorrect for this DBCC statement.

Suraj Kumar · Answer

If you will read my previous answer carefully then you could get the solution. By the way you can also read and follow the answer available at the below link. https://stackoverflow.com/questions/19155775/how-to-update-identity-column-in-sql-server

amit · Answer

please clarify that I wants to the primary key for example my auto-increment value is 1000 then continue...10001 ....10002 i wants to change in place of 1000 is 1200 ?

Suraj Kumar · Answer

Turn off the identity column by editing the table definition after opening in the design mode. After updating the value you can change it again into the identity column. Alternativel, you can delete the existing record and enable the identity insert and insert the new record for the desired column values as shown below. set identity_insert YourTable ON You can also reset the identity column values by the new one as shown below. DBCC CHECKIDENT( 'tableName' , RESEED, NEW_RESEED_VALUE) One thing you need to remember even after removing the identity column you need to insert / update with the unique value as you have said your column ins the primary key which does not accept the duplicate values in any case.

how to update primary key which is auto inrement in sql

Answers (5)