0

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!

  • 1
    Have you looked into the manual? The syntax is pretty much clear with regard to what you are looking for. – Andriy M May 18 '16 at 5:37
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);

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.