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