identity_val_local() for multi-row insert

[Haolan Qin <hq...@nsirad.com>]

Hi there,

 

The documentation says the following.

 

"The result of the function is not affected by the following: 

*	A single row INSERT statement with a VALUES clause for a table
without an identity column
*	A multiple row INSERT statement with a VALUES clause
*	An INSERT statement with a fullselect"

 

Is this a "feature"? If so what's the rationale behind it? If it's not a
feature then I assume it's some sort of a bug that's really hard to fix?
Is there any workaround at all (to get the correct identity value after
an "insert into...select...")?

 

We are currently using HSQL and would really like to switch to Derby but
this is a roadblock. 

 

We would really appreciate it if you could shed some light on this.

 

Thank you,

Haolan Qin (Neurostar Solutions)

Re: identity_val_local() for multi-row insert

[Daniel John Debrunner <dj...@apache.org>]

Haolan Qin wrote:
> Hi there,
> 
>  
> 
> The documentation says the following.
> 
>  
> 
> “The result of the function is not affected by the following:
> 
>     * A single row INSERT statement with a VALUES clause for a table
>       without an identity column
>     * A multiple row INSERT statement with a VALUES clause
>     * An INSERT statement with a fullselect”
> 
>  
> 
> Is this a “feature”? If so what’s the rationale behind it? If it’s not a 
> feature then I assume it’s some sort of a bug that’s really hard to fix? 
> Is there any workaround at all (to get the correct identity value after 
> an “insert into…select…”)?

Which is the "correct" identity value if multiple rows were inserted, 
since in SQL no ordering is defined here?

Dan.