Thanks Knut! I removed the extra INSERT statements and simply appended the values as you shown in your example and it worked great. Thanks for clarifying.
-----Original Message----- From: [email protected] [mailto:[email protected]] Sent: Tuesday, October 13, 2009 10:06 AM To: Derby Discussion Subject: Re: auto generated ids Brian Spindler <[email protected]> writes: > I have a table let's say FOO with a column name 'ID' and it is auto > increment. > > Now I want to insert one row into that table and then insert 10 rows > into an adjacency table with the new generated id. I thought something > like this might work: > > INSERT INTO FOO(NAME) VALUES ('BAR'); > INSERT INTO ADJTBL (FOO_ID, BAR_NAME) > VALUES ( > (SELECT DISTINCT IDENTITY_VAL_LOCAL() FROM FOO), > 'MyName'); > ... > > and while the first INSERT works the IDENTITY_VAL_LOCAL() then reads the > generated id from ADJTBL as opposed to the FOO table. What am I doing > wrong? Is this possible? I am running this from a .sql file within IJ > tool. IDENTITY_VAL_LOCAL() returns the most recently assigned value of an identity column for a connection in a single-row INSERT statement, so once you've inserted a new row into ADJTBL, the return value from IDENTITY_VAL_LOCAL() will change. See http://db.apache.org/derby/docs/10.5/ref/rrefidentityvallocal.html. Inserting all 10 rows into ADJTBL in one INSERT statement should work, though: INSERT INTO FOO(NAME) VALUES ('BAR'); INSERT INTO ADJTBL (FOO_ID, BAR_NAME) VALUES (IDENTITY_VAL_LOCAL(), 'MyName1'), (IDENTITY_VAL_LOCAL(), 'MyName2'), ... (IDENTITY_VAL_LOCAL(), 'MyName10'); -- Knut Anders
