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.

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

> ? 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."

>

> 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

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

>

> - Paul

>