Oracle Database Remove duplicate rows across schema-CodePudding

I have a requirement where i need to move data from one schema to another schema. While moving rows i need to make sure that if there are matching rows in both schemas, the duplicate rows shall not be inserted.

So, i have table A in Schema A.

And same Table A in Schema B.

I need to move data from Schema A to B. In this case since Speed is available in both, it shouldn't be inserted in Schema B. How can i write the insert query handling this condition?

CodePudding user response：

You could use an INSERT INTO ... SELECT with some exists logic:

INSERT INTO TableB (EMP_ID, NAME, TRANSACTION_Date)
SELECT EMP_ID, NAME, TRANSACTION_Date
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.NAME = a.NAME);

If you want to consider the combination of EMP_ID and NAME as determining a duplicate, then use:

INSERT INTO TableB (EMP_ID, NAME, TRANSACTION_Date)
SELECT EMP_ID, NAME, TRANSACTION_Date
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b
                  WHERE b.EMP_ID = a.EMP_ID AND b.NAME = a.NAME);

CodePudding user response：

How about merge?

merge into schema_b.table_a b
  using schema_a.table_a a
  on (a.name = b.name)
when not matched then insert (emp_id, name, transaction_date)
values (a.emp_id, a.name, a.transaction_date);