TSQL:


If you have problems when inserting records into a table with an identity field, because the identity field value is out sink.

Use the following TSQL command to check the identity value DBCC CHECKIDENT ("[TABLE NAME]", NORESEED); where [TABLE NAME] is the name of your table.

So for example DBCC CHECKIDENT ("Products", NORESEED);

If you need to set the identity value use the following TSQL command DBCC CHECKIDENT ("[TABLE NAME]", RESEED, [NUMBER]); where [TABLE NAME] is your table name and [NUMBER] is the integer value of the new identity value.

For example DBCC CHECKIDENT ("Products", RESEED, 300);


When running UPDATE commands or INSERT commands, you can use BEGIN TRY commands and BEGIN TRANSACTION commands to rollback any changes if any of the script should fail.

Example script: