No auto increment for the PK on duplicate insert with IGNORE_DUP_KEY = ON

Question

I set IGNORE_DUP_KEY = ON so there is no warning when we try to insert lots of data. However, the primary key does increase for each duplicate row, so one could go from Id 100 to 120 when you have 20 duplicate rows after each other. Is there any way to let it continue at 101 without manually setting the value? I'm using SqlBulkCopy and Entity Framework and I'm not interested in a SQL query to workaround this problem (e.g. using NOT EXISTS). Of course, we could check first if a record already exists but we prefer not to for performance reasons.

Why do you care of the actual PK value? – zerkms Jan 01 '13 at 22:19 — zerkms, Jan 01 '13 at 22:19

score 0 · Accepted Answer · answered Jan 01 '13 at 22:05

0

If you require continuous identity values then IDENTITY is not for you. There is absolutely no guarantee that identity values cannot go missing. You scenario is not the only case where this happens.

Either manage IDs yourself (using a sequence, for example; not easy to integrate into a bulk load) or give up on the requirement to have uninterrupted IDs.

answered Jan 01 '13 at 22:05

usr

168,620
35
240
369

That's what I thought, thanks for the answer :) – The Cookies Dog Jan 02 '13 at 08:25

No auto increment for the PK on duplicate insert with IGNORE_DUP_KEY = ON

1 Answers1