|
If the insert is not inserting but:
|
|
Hm, as "ON EXISTING" requires a PK lookup, you can only have "collisions" with existing rows if the PK is specified in the INSERT statement itself, and IMHO therefore the PK cannot be the DEFAULT AUTOINCREMENT column here. - So you are using a different column with DEFAULT AUTOINCREMENT? The docs on the INSERT statement do explain that as follows:
So i assume @@IDENTITY would skip over these values as well. Don't know for ON EXISTING UPDATE - with DEFAULTS OFF, there should not be a need to calculate a fresh value, and for DEFAULTS ON, I'd think it would supply new AUTOINCREMENT values and therefore would naturally increase these values. We have sometimes the case, that the PKey is not the autoincrement, but the autoincrement column is used for FKey relations to other tables, because it is more convenient than the PKey, which might consist of multiple columns. By the way, I wouldn't expect that the autoincrement field is changed implicitly through the on existing update.
(12 Apr '13, 07:04)
Martin
Replies hidden
That is depending on the DEFAULTS option, cf. the docs:
So the "default" (pun intended) here is to leave the field unchanged.
(12 Apr '13, 07:24)
Volker Barth
thanks, good to know
(12 Apr '13, 08:04)
Martin
|
BTW, are you asking for INSERT ... VALUES (usually a one row insertion) or for INSERT SELECT, aka mass insertions?
the first variant