cancel
Showing results for 
Search instead for 
Did you mean: 

What happens to @@identity on existing skip?

MCMartin
Participant
2,987

If the insert is not inserting but:

  1. What will be the value of @@identity, if an "on existing skip" insert statement is skipping?
  2. What will be the value of @@identity, if an "on existing update" insert statement is updating?
VolkerBarth
Contributor
0 Kudos

BTW, are you asking for INSERT ... VALUES (usually a one row insertion) or for INSERT SELECT, aka mass insertions?

MCMartin
Participant
0 Kudos

the first variant

Accepted Solutions (0)

Answers (1)

Answers (1)

VolkerBarth
Contributor

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:

When using the ON EXISTING SKIP and ON EXISTING ERROR clauses, if the table contains default columns, the server computes the default values even for rows that already exist. As a result, default values such as AUTOINCREMENT cause side effects even for skipped rows. In this case of AUTOINCREMENT, this results in skipped values in the AUTOINCREMENT sequence.

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.

MCMartin
Participant
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

By the way, I wouldn't expect that the autoincrement field is changed implicitly through the on existing update.

That is depending on the DEFAULTS option, cf. the docs:

When using the ON EXISTING UPDATE clause with columns that have defaults (including DEFAULT AUTOINCREMENT columns), you can further specify whether to update the column value with the default values by specifying ON EXISTING UPDATE DEFAULTS ON, or leave the column value as it is by specifying ON EXISTING UPDATE DEFAULTS OFF. If nothing is specified, the default behavior is ON EXISTING UPDATE DEFAULTS OFF.

So the "default" (pun intended) here is to leave the field unchanged.

MCMartin
Participant
0 Kudos

thanks, good to know