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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
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
