Update: This question is still OPEN because @@IDENTITY is returning the WRONG VALUE... if that is expected behavior, it is NOT EXPECTED by Deltas or Gammas, probably not even by Betas; i.e., it is NOT EXPECTED by ordinary folk... so, at the very least it should be explained and documented.


I want to do an INSERT and get the identity value all-in-one-operation using the "dml derived table" feature of the FROM clause introduced in Version 12.

Tests show that the previous @@IDENTITY value is returned to SELECT @@IDENTITY when there is an INSERT in the FROM clause that causes a DEFAULT AUTOINCREMENT column to be incremented.

This behavior feels like either (a) expected but undocumented and therefore surprising behavior, or (b) a feature-not-yet-considered (surely not a bug!)

(I am HOPING for Door Number Three: I'm missing something obvious)

Here's a demonstration that was run twice in a row on both 12.0.1.3298 and 16.0.0.1512, so the "previous @@IDENTITY value" starts out as 3 for the second test:

BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t (
   pkey  INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   data  VARCHAR ( 10 ) );

SELECT @@IDENTITY AS "identity1a WRONG",
       old.*
  FROM ( INSERT t ( data ) VALUES ( 'Hello' ) ) REFERENCING ( FINAL AS old );

SELECT @@IDENTITY AS "identity1b CORRECT",
       t.*
  FROM t
 ORDER BY pkey;

SELECT @@IDENTITY AS "identity2a WRONG",
       old.*
  FROM ( INSERT t ( data ) VALUES ( 'World' ) ) REFERENCING ( FINAL AS old );

SELECT @@IDENTITY AS "identity2b CORRECT",
       t.*
  FROM t
 ORDER BY pkey;

SELECT @@IDENTITY AS "identity3a WRONG",
       old.*
  FROM ( INSERT t ( data ) VALUES ( 'World' ) ) REFERENCING ( FINAL AS old );

SELECT @@IDENTITY AS "identity3b CORRECT",
       t.*
  FROM t
 ORDER BY pkey;

identity1a WRONG        pkey data       
-------------------- ----------- ---------- 
                   3           1 Hello      
(1 rows)

identity1b CORRECT        pkey data       
-------------------- ----------- ---------- 
                   1           1 Hello      
(1 rows)

identity2a WRONG        pkey data       
-------------------- ----------- ---------- 
                   1           2 World      
(1 rows)

identity2b CORRECT        pkey data       
-------------------- ----------- ---------- 
                   2           1 Hello      
                   2           2 World      
(2 rows)

identity3a WRONG        pkey data       
-------------------- ----------- ---------- 
                   2           3 World      
(1 rows)
Execution time: 0.525 seconds
Execution time: 0.005 seconds
Execution time: 0.036 seconds
Execution time: 0.03 seconds
Execution time: 0.008 seconds
Execution time: 0.015 seconds

identity3b CORRECT        pkey data       
-------------------- ----------- ---------- 
                   3           1 Hello      
                   3           2 World      
                   3           3 World      
(3 rows)
Execution time: 0.004 seconds
Execution time: 0.014 seconds

asked 18 May '13, 08:19

Breck%20Carter's gravatar image

Breck Carter
27.0k424582830
accept rate: 21%

edited 23 May '13, 08:05

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265

Yes, I clearly share the impression that the wrong result of @@IDENTITY is unexpected, err, a bug... - don't know what BNW category I'm representing:)

(23 May '13, 05:43) Volker Barth

If you do like to use a DML derived query here to get the freshly inserted value, why would you not directly select the according column - as your sample queries clearly show, selecting that value DOES work (sorry, I think "new" is a more appropriate alias here:):

SELECT new_t.pkey
FROM ( INSERT t ( data ) VALUES ( 'World II' ) )
   REFERENCING ( FINAL AS new_t );

will return 4 for the next inserted row.

Yes, that does not answer your question, apparently.


EDIT: Oops, "new" is as keyword, so new_t seems easier to use - though I'm quite sure it has worked when is wrote that on a different box with 12.0.1.3867...

permanent link

answered 19 May '13, 17:24

Volker%20Barth's gravatar image

Volker Barth
29.6k293444650
accept rate: 32%

edited 21 May '13, 03:32

It might not be an EXACT answer but it is PERFECTLY ACCEPTABLE!

(and about using "new" as the alias, all I can say is "doh!" :)

(19 May '13, 18:07) Breck Carter
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:

×78
×8
×5

question asked: 18 May '13, 08:19

question was seen: 3,019 times

last updated: 23 May '13, 08:05