Please see the answers inline.

On 5/13/05, Paul J. Lucas <pauljlucas@mac.com> 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
>