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: