Please see the answers inline.
On 5/13/05, Paul J. Lucas wrote:
> On Fri, 13 May 2005, Daniel John Debrunner wrote:
>
> > So I think the answer to Paul's question is not answered by that
> > documentation.
>
> Right.
>
> > Does it mean the most recent INSERT for that connection or the most
> > recent insert to the database.
>
> What about the most recent insert for a table? Does:
>
> SELECT IDENTITY_VAL_LOCAL() FROM mytable1;
>
> really only return IDENTITY_VAL_LOCAL() for *that* table or is
> the above deceptively equivalent to:
>
> VALUES IDENTITY_VAL_LOCAL();
>
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. And hence, return from SELECT
IDENTITY_VAL_LOCAL() FROM mytable1 depends on what kind of insert statements
went in before the select in the transaction.
> ? More explicitly, if I do:
>
> INSERT INTO mytable1 ... ;
> INSERT INTO mytable2 ... ;
> SELECT IDENTITY_VAL_LOCAL() FROM mytable1;
> SELECT IDENTITY_VAL_LOCAL() FROM mytable2;
>
> where both tables have an IDENTITY column, so I get the correct
> values back from both selects?
>
Let me repeat following line of the documentation again here.
"The IDENTITY_VAL_LOCAL function is a non-deterministic function that
returns *the most recently assigned value for an identity column*, where the
assignment occurred as a result of a single row INSERT statement using a
VALUES clause."
So, for your particular example (assuming both mytable1 and mytable2 have
identity column and assuming that the inserts are single row inserts using a
VALUES clause), both the select sqls will return the the value that went
into mytable2's identity column.
> - Paul
>