The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
26.6k418576824
accept rate: 21%

edited 23 May '13, 08:05

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262

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.3k287438645
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: 2,899 times

last updated: 23 May '13, 08:05