Load two related tables in an Oracle database

Question

I've seen many similar questions & answers, but they've used other DB-specific tricks, or done it in code, etc. I'm looking for a straight SQL batch file solution (if it exists).

I have two tables with a parent/child relationship, call them Runs & Run_Values.
Runs has an "auto" generated PK, runID (a sequency & a trigger), and two colums, Model_Type & Time that ALSO uniquely identify the row (enforced with a constraint). For each row in Run, there are many entries in Run_Values, which has two columns, RunId & Value.

I'd like to generate something like this from the process that makes the data (mix of sql I know exists and SQL as I'd like it):

insert into Runs (Model_Type, Time) values ('model1', to_date('01-01-2009 14:47:00', 'MM-DD-YYYY HH24:MI:SS'));
set someVariable = SELECT runId FROM Runs WHERE Model_Type like 'model1' and Time =  to_date('01-01-2009 14:47:00', 'MM-DD-YYYY HH24:MI:SS'));
insert into Run_Values (run_id, Value) values (someVariable, 1.0);
etc - lots more insert statements with Values for this model run.

Any thoughts, references, etc are appreciated.

Answer 1

The trick is to use the CURRVAL of the sequence. Even though it is set in a database trigger, you can just use it.

An example:

SQL> create table runs
  2  ( runid      number
  3  , model_type varchar2(6)
  4  , time       date
  5  )
  6  /

Table created.

SQL> create sequence run_seq start with 1 increment by 1 cache 100
  2  /

Sequence created.

SQL> create trigger run_bri
  2  before insert on runs
  3  for each row
  4  begin
  5    select run_seq.nextval
  6      into :new.runid
  7      from dual
  8    ;
  9  end;
 10  /

Trigger created.

SQL> create table run_values
  2  ( run_id number
  3  , value number(3,1)
  4  )
  5  /

Table created.

SQL> insert into runs (model_type, time) values ('model1', to_date('01-01-2009 14:47:00', 'mm-dd-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into run_values (run_id, value) values (run_seq.currval, 1.0);

1 row created.

SQL> insert into run_values (run_id, value) values (run_seq.currval, 2.0);

1 row created.

SQL> insert into runs (model_type, time) values ('model2', to_date('01-01-2009 15:47:00', 'mm-dd-yyyy hh24:mi:ss'));

1 row created.

SQL> insert into run_values (run_id, value) values (run_seq.currval, 3.0);

1 row created.

SQL> insert into run_values (run_id, value) values (run_seq.currval, 4.0);

1 row created.

SQL> insert into run_values (run_id, value) values (run_seq.currval, 5.0);

1 row created.

SQL> select * from runs
  2  /

     RUNID MODEL_ TIME
---------- ------ -------------------
         1 model1 01-01-2009 14:47:00
         2 model2 01-01-2009 15:47:00

2 rows selected.

SQL> select * from run_values
  2  /

    RUN_ID      VALUE
---------- ----------
         1          1
         1          2
         2          3
         2          4
         2          5

5 rows selected.

Regards, Rob.