Please see the answers inline.
On 5/13/05, Paul J. Lucas <firstname.lastname@example.org
> On Fri, 13 May 2005, Daniel John Debrunner wrote:
> > So I think the answer to Paul's question is not answered by that
> > documentation.
> > 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.
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