This code will do the following:
1. Display the current INDENT_CURRENT value for the identity column for the specified table. This value should be the SAME value as the highest value for the column in the table.
2. This SELECT gives you the highest assigned identity value in the table
3. The SQL statement performs the resetting of the IDENT_CURRENT value should it not be correct. The next INSERT to be performed no this table will use this value as a base + the increment value set for the table.
I had a situation in production where the values of both queries #1 and #2 were the same and the error still came up. I ran query #3 incrementing the reseed value +1 of the current top seed value. That fixed the issue.
|
1 2 3 4 5 6 7 8 9 |
-- 1. Gets the current set internal INDEX value for the specified table. SELECT IDENT_CURRENT('tableResources') AS resourceID -- 2. Displays the highest used IDENT in the identity column. SELECT TOP 1 * FROM tableResources r ORDER by r.resourceID DESC -- 3. This command resets the internal INDENT_CURRENT value to the highest used value. DBCC CHECKIDENT (tableResources, RESEED, 8911792) |
Views – 1672