Re: Efficient Insert statement for one record into multiple

["Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>]

>> 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