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!
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);
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
);
