INSERT INTO with Sequence.nexval

[ksbigfoot]

I didn't want to use a trigger on INSERT in my table.
I am wondering how to get this query to work (I simplified it for this post)

INSERT INTO Table1 (Sequence.nextval AS Table1_ID, FirstName, LastName)
(select '' AS Table1_ID, FirstName, LastName
from Table2
minus
select '' As Table1_ID, FirstName, LastName from Table1)
-- Rows in Table2 that are not in Table1
select

Problem I am having is I am not sure how to insert the sequence.nextval into the Table1_ID field.

 

[Ascalonian]

You need to select that value into a variable first, such as:

sequenceValue NUMBER;

SELECT Sequence.nextval
  INTO sequenceValue
  FROM DUAL;

And then use that value in the insert statement

 

[ksbigfoot]

If I read the value into a variable, when I run the query above, would every record contain the same value in Table1_ID?

 

[Ascalonian]

No, you would need to do a loop. Create a cursor from the inner select statement:

CURSOR table2_cur
IS
  select FirstName, LastName
    from Table2
  minus
  select FirstName, LastName 
    from Table1;

Then use this cursor to loop through everything:

<< main_loop >>
FOR table2_rec IN table2_curs
LOOP
    -- Select nextval as described before

    INSERT INTO Table1(Table1_Id, FirstName, LastName)
          VALUES (Sequence.NEXTVAL, table2_rec.FirstName, table2_rec.LastName);

END LOOP main_loop;

 

[anto2]

I think this would work for you assuming you have table2_id field on table1

insert into table1 (table1_id,table2_id,forename,surname)
select sequence.nextval, table2.table2_id,table2.forename,table2.surname
from table2
where table2.table2_id not in (select table2_id
from table1);