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:


BEGIN TRY
  BEGIN TRANSACTION
    -- INSERT YOUR UPDATE OR INSERT SCRIPT HERE...
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION
END CATCH