Mamta Satoor wrote: > The SELECT IDENTITY_VAL_LOCAL() FROM mytable1 will return the value that > got into generated for _any_ table with identity column using single row > insert with values clause in the current transaction.
Except it doesn't behave like that, with respect to the *current transaction*. Derby's implementation returns the last identity value for a single row INSERT statement within the same connection. See the example below, and note auto commit is true. And it makes no sense to do a SELECT IDENTITY_VAL_LOCAL() FROM mytable1, that will just return the same value multiple times (once per row in the table) and the value will be the last identity value for a single row INSERT statement within the same connection. Dan. ij> connect 'jdbc:derby:foo;create=true'; ij> create table t (id int generated always as identity, d int); 0 rows inserted/updated/deleted ij> insert into t(d) values(88); 1 row inserted/updated/deleted ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 1 row selected ij> select * from t; ID |D ----------------------- 1 |88 1 row selected ij> values IDENTITY_VAL_LOCAL(); 1 ------------------------------- 1 1 row selected
