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

entity-framework sqlbulkcopy sql-server

Question

In order to prevent a warning when we attempt to input a lot of data, I set IGNORE DUP KEY = ON. However, the main key does rise for each duplicate row, thus if 20 duplicate rows are stacked on top of one another, the Id may change from 100 to 120. Is there a method to keep it at 101 without having to manually adjust the value? SqlBulkCopy and Entity Framework are what I'm using, and I'm not interested in utilizing a SQL query to get past this issue (e.g. using NOT EXISTS). Of course, we could first check whether a record already exists, but for performance reasons, we prefer not to.

1
0
1/1/2013 4:08:25 PM

Accepted Answer

If continuous identity values are needed, thenIDENTITY for you is not. Identity values cannot be guaranteed to remain intact at all times. This may occur in situations other than the one you describe.

Either take control of ID management yourself (for example, using a sequence; difficult to incorporate into a bulk load) or give up on the need for continuous IDs.

0
1/1/2013 10:05:05 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow