Insert Loop from select in Oracle

Question

I insert 1 rows successfully, but I want to insert rows using loop. The purpose is want to insert into table2 values of table1 that is not existed. My query like that:

FOR cl1 IN (SELECT tb1.cl1 FROM table1 tb1 WHERE tb1.cl1 NOT IN(SELECT tb2.cl1 FROM table2 tb2))
LOOP
  INSERT INTO table2 VALUES (cl1,'123456',sysdate);
END LOOP

user330315 · Accepted Answer · 2014-07-07 10:05:18Z

1

No need for a loop:

INSERT INTO table2 
SELECT tb1.cl1, '123456', sysdate
FROM table1 tb1 
WHERE tb1.cl1 NOT IN(SELECT tb2.cl1 FROM table2 tb2)

Your sub-query using NOT IN will not work as you expect it, if the column cl1 contains NULL values.

It is also considered good coding style to explicitly list all columns in the insert statement: insert into table2 (col1, col2, col3) ...

answered Jul 7, 2014 at 10:05

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert Loop from select in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related