I have a recurring problem inserting rows into tables that have a column that is “int IDENTITY PRIMARY KEY.” At some point in the life of the table, I get “PRIMARY KEY violation” errors when I try to insert data (of course not specifying an explicit value for the IDENTITY column). Can you tell me what I’m doing wrong, and how to fix it without rebuilding the table (which is what I do at the moment)?
There is a DBCC option of checkident (table name). This checks to see that the current value is valid. If it is not, it resets it to the maximum value found in the table. I believe they fixed this problem with one of the service patches. So I would also recommend that you make sure you are on the latest service pack.