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?

asked 12 Apr '13, 05:07

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

edited 12 Apr '13, 06:47

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

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

(12 Apr '13, 06:23) Volker Barth
Replies hidden

the first variant

(12 Apr '13, 07:00) Martin

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.

permanent link

answered 12 Apr '13, 06:13

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 12 Apr '13, 06:21

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

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.

(12 Apr '13, 07:24) Volker Barth

thanks, good to know

(12 Apr '13, 08:04) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×34
×14
×8
×7

question asked: 12 Apr '13, 05:07

question was seen: 2,651 times

last updated: 12 Apr '13, 08:04