db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Paul J DeCoursey <p...@decoursey.net>
Subject Re: Retrieving the identity column value after an insert
Date Tue, 01 Aug 2006 14:47:15 GMT
Mark Ashworth wrote:
> Hi,
>
> The problem is when you want to insert rows into other tables for each of
> the rows that have been inserted into to the user's table and those tables
> have identity fields. In this case it seems that without a variable to hold
> the userid, the IDENTITY_VAL_LOCAL() returns the new identity value from
> those other tables (which is to be expected).
>
> Regards,
> Mark P Ashworth
>
> -----Original Message-----
> From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM] 
> Sent: 01 August 2006 03:27 PM
> To: Derby Discussion
> Subject: Re: Retrieving the identity column value after an insert
>
> Mark Ashworth wrote:
>   
>> Good Day,
>>
>>  
>>
>> I would like to run the following SQL in ij
>>
>>  
>>
>> INSERT INTO users (name) VALUES ('test');
>>
>>  
>>
>> And after the insert statement use the inserted id value to complete 
>> other inserts in other tables.
>>
>>  
>>
>> INSERT INTO attr (user_id, value) VALUES( INDENTITY_VAL_LOCAL(), 'test 
>> attr');
>>
>> INSERT INTO attr (user_id, value) VALUES( INDENTITY_VAL_LOCAL(), 'test 
>> attr2'); <!-Problem here because the IDENTITY_VAL_LOCAL() now contains 
>> the id that was generated in the previous statement.
>>
>>  
>>
>> How do I assign the identity value to a variable?
>>     
>
> Hello Mark,
>
> I don't know how to assign the identity value to a variable, but if you 
> can use multiple row INSERT statements, you should be able to do what 
> you want.
>
> INSERT INTO users (name) VALUES ('test');
> INSERT INTO attr (user_id,value) VALUES
> 	(IDENTITY_VAL_LOCAL(), 'test attr'),
> 	(IDENTITY_VAL_LOCAL(), 'test attr2');
>
> The multiple row insert will not update the value of IDENTITY_VAL_LOCAL.
> Note that not all databases support this form of the INSERT statement.
>
>
>
>
> Regards,
>   
try SELECT IDENTITY_VAL_LOCAL() FROM users, I think that the 
IDENTITY_VAL_LOCAL() is per table, I have some stored procedures that do 
this and it seems to work.  I'm not for certain that it is correct.

Paul



Mime
View raw message