INSERT OR REPLACE INTO + SELECT as source in Oracle

Question

I have two tables A and B.

I want to insert B's content into table A. The issue is there's a row in B that already exists in A.

I've searched and I found some answers talking about MERGE, but no one says how to use it with a SELECT as source.

In summary, I would like to use this statement

INSERT INTO A (data1, data2, dataX)
SELECT B.data1, 'String', B.data2, B.data3
FROM B;

But avoiding existing rows.

Thank you!

• Andriy M: Have you looked into the manual? The syntax is pretty much clear with regard to what you are looking for.

Answer 1

Table1: A (DATA1, DATA2, DATA3)

Table2: B (DATA1, DATA2, DATA3)

Assumption: First fieldName of both table is primary key

MERGE INTO A TA USING (SELECT * FROM B) TB ON (TA.DATA1 = TB.DATA1) WHEN NOT MATCHED THEN INSERT (TA.DATA1, TA.DATA2, TA.DATA3) VALUES (TB.DATA1, TB.DATA2, TB.DATA3);

Answer 2

In such case you don't have to use MERGE. Assuming data1 column can be used to check whether the record exists, the following should do the job :

INSERT INTO A (data1, data2, dataX)
SELECT B.data1, B.data2, B.data3
FROM B b WHERE NOT EXISTS 
(
 SELECT NULL FROM A a WHERE a.data1=b.data1
);