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.
Thanks,
Brian
