Re: Efficient Insert statement for one record into multiple

>> I have this task that I need to insert one user record into to tables
A
>> and B.
>>
>> So I'd like to do something like this
>>
>> insert into A (user_name, value2 ) values ( "foo", "foo2")
>>
>> AND
>>
>> insert into B ( id_from_a , statistic_data) values ( 23, "bla")
>>
>> How is this done the best way? I would like to do this in one call.
>
> If you know the name of the sequence for the primary key in table A,
say
> "primary_A", then use the following to insert into table B direct
after
> you inserted into table A in the same connection:
>
> insert into B (id_from_a, statistic_data) values(
currval("primary_A"),
> bla")
>
> This works only if your insertion into table A called in some form
> nextval("primary_A"), may it be as default value or in the
> insertion-statement itself.

This is _bad_.

What if somebody did a nextval("primary_A") between the insert into A
and the insert into B?

If there is a sequence "primary_A" for table A, you should proceed
as follows:

- select nextval("primary_A")
- then insert into A with this primary key
- then insert into B with the same key

That should all be in a single transaction!

Yours,
Laurenz Albe