Re: SQL INSERT INTO multiple Tables- HELP!!!

In article <01bb874c$a8b94900$708264ce_at_raevaen.dimensional.com>, raevaen_at_dimensional.com says...
>
>I need to do a nested parameter query and really need some help
>Table 1 has P_ID, a counter field. When I INSERT INTO Table 1, I also need
>to INSERT the P_ID (unknown until after the insert when it will be
>incremented) into Table 2. Alone with all this, some PARAMETERS need to go
>into Table 1 and Table 2.
>
>HELP!!!!
>
>It would be nice if something like:
>
>Parameters newName, newYear;
>INSERT INTO Table1 INNER JOIN Table2 ON Table1.P_ID=Table2.P_ID
>(Table1.Name, Table2.P_ID, Table2.Year)
>VALUES (newName, Table1.P_ID, newYear);
>
>could be done but this is totally incorrect. ACK! but it kinda shows what I
>need to do. HELP!!!
>
>raevaen_at_dimensional.com

Hi.

What ver of Rdb are you using?
If you are running Rdb6.0+, then maybe try a compound statement made into a user-defined procedure.

If I understand your problem, here is a guide:

1. pass all values to be inserted into Table1 into the function.
2. define a dbkey type variable named tmp_dbk.
3. INSERT INTO table1 VALUES ... RETURNING DBKEY tmp_dbk;
4. SELECT ... FROM table1 WHERE DBKEY = :tmp_dbk;
5. INSERT INTO table2 VALUES ...;
6. end of function

The data to be inserted into table2 at step 5 would come from the select in step 4 and any parameters passed in step 1.

If you're running sub-6.0, then you're S.O.L.

Hope this helps.

Ron Johnson Received on Sun Aug 18 1996 - 00:00:00 CEST