Last Row Inserted

Hi,

I'm using Oracle 10G on 64 bit Redhat Linux.

I need to perform a bulk insert:
create table table1 (
  pk1 number primary key,
  value1 number
);

create table table2 (
  pk2 number primary key,
  value2 number
);

create sequence seq1 start with 1;
create sequence seq2 start with 1;

insert into table1 (pk1, value1)
select seq1.nextval, object_id
  from user_objects;

Next I want to insert these values into table2, somehow returning the last pk1 value:

insert into table2 (pk2, value2)
select seq2.nextval, value1
  from table1
 where pk1 > v_last_known_pk1_value
returning max(pk1) into v_max_pk1_value;

The aim is to replicate new data from table1 in table2, using a sequence specific to table2. Many other process write to table2 using seq2.

Does anybody know of any efficient/elegant way to do this, I could use a cursor and record the pk1 value, setting v_max_pk1_value on each iteration, but I believe (correct me) that this will not be as efficient as a bulk insert.

I have not used Oracle Replication before, nor do I know enough about it to know if it could solve my problem of replicating table1's data while allowing other processes to write to table2. Our Sysdba told me he has not installed it as a feature but can if I request it.

Many Thanks,

Kevin Received on Fri Sep 02 2005 - 10:20:24 CDT