Insert row with id into a table with auto increment on

Question

I'm wondering if it is possible to "re-insert" a row back into a table that has auto-increment on the Id column. I have an "unprocessed" table and a "processed" table, "unprocessed" has auto-increment and when they are processed they move to the "processed" table and keep the same id. It is possible for a row to move back to "unprocessed" so I would need to be able to "re-insert" this row with the same Id again. Just in case I wasn't clear, I know from the definition of the problem that there will never be a collision between the Ids.

Also, this is being done on SQL Server 2008 R2

Thanks, Tom

Answer 1

Yes this is possible, you simply supply the ID column with the desired value in your INSERT statement and enable "set identity_insert" first for the desired table (and disable it afterwards for safty)

This is pretty much this (pseudo code):

INSERT #1 --created id 1
INSERT #2 --created 2
INSERT #3 -- created 3
DELETE 2 --2 gone, leaves a gap
set identity_insert your_table on
INSERT VALUES ( 2, ... ) -- filled gap again
set identity_insert your_table off

Anyway, I would recommend using a flag in one table, if nothing speaks against it.

So you can simply flip the flag and you will never have to struggle/ensure your ids