Re: Need magic for inserting in 2 tables

On Sun, Oct 3, 2010 at 4:14 PM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> insert into staff ( company_fk, ..., department_fk )
> select  company_fk, ..., department_fk
> from     departments,   companies,   company_2_project  AS c2p
> where  company_id      =   c2p.company_fk
>    and c2p.project_fk    =   42
>    and department_id  in  ( 40, 50 );
>
> step 2 would be to link those new blank staff records to project 42 by
> inserting a record into staff_2_project for every new staff_id.
>
> How can I find the new staff_ids while making sure I don't insert ids from
> other sessions?
> Is there an elegant way in SQL ?

Use returning?

insert into .....
yada
returning field1, field2, field3

-- To understand recursion, one must first understand recursion.