db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Retrieving the identity column value after an insert
Date Tue, 01 Aug 2006 13:27:04 GMT
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,
-- 
Kristian

> 
>  
> 
> Kind regards,
> 
> Mark Ashworth
> *Senior Software Developer*
> 
> Cell: +27 84 235 3554
> Email: *marka@cibecs.com <mailto:marka@cibecs.com>*
> 
>  
> 
> 	
> 
> Tel: +27 (011) 253 7600
> Fax: 0866 800 571
> **www.cibecs.com <http://www.cibecs.com>**
> 
> 	
> 
> 	
> 
>  
> 
> 	
> 
> * Everything in this e-mail and attachments relating to the official 
> business of CIBECS (Pty) Ltd (Reg. Number 1981/05934/07) is proprietary 
> to the company. It is confidential, legally privileged and protected by 
> law. CIBECS does not own and endorse any other content. Views and 
> opinions are those of the sender unless clearly stated as being that of 
> CIBECS. The person addressed in the e-mail is the sole authorised 
> recipient. Please notify the sender immediately if it has 
> unintentionally reached you and do not read, disclose or use the content 
> in any way. Whilst all reasonable steps are taken to ensure the accuracy 
> and integrity of information and data transmitted electronically and to 
> preserve the confidentiality thereof, no liability or responsibility 
> whatsoever is accepted if information or data is, for whatever reason, 
> corrupted or does not reach its intended destination.
> 
>  
> 


Mime
View raw message