New answers tagged auto-increment
Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway. Example: IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL DROP TABLE ...
LOAD the new data into a separate table (staging), without including the AUTO_INCREMENT field. Decide whether there are any duplicates that need to be avoided. INSERT ... SELECT (without id) to copy the "new" data into the real table. DROP TABLE staging;
As soon as you insert the data in new table, your next auto_increment value will be Max(id) + 1. Which means if you insert new row, the id given will be highest number in the id column. This post may be relevant to your question.
Top 50 recent answers are included