How do I get SELECT @@IDENTITY FROM ( INSERT ... ) to work properly?

[Breck_Carter]

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

[VolkerBarth]

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:)

[VolkerBarth]

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...

[Breck_Carter]

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!" 🙂