Copying data from table A to B while rejecting duplicates
An easy way to copy data from one table to another while ignoring duplicate records is by using the DBMS_ERRLOG package.
The DBMS_ERRLOG package provides a procedure that enables you to create
an error logging table so that DML operations can continue after encountering
errors rather than abort and roll back.
To start off you need to create a logging table.
The syntax for creating the table is as follows:
DBMS_ERRLOG.CREATE_ERROR_LOG (
dml_table_name IN VARCHAR2,
err_log_table_name IN VARCHAR2 := NULL,
err_log_table_owner IN VARCHAR2 := NULL,
err_log_table_space IN VARCHAR2 := NULL,
skip_unsupported IN BOOLEAN := FALSE);
To creates a logging table, only the table name is required.
exec dbms_errlog.create_error_log('TableA');
This create a table called ERR$_TableA in the current schema based on the table TableA.
You can then copy data from the source table to the target. Duplicate records are rejected and logged but the statement will not fail.
INSERT INTO my_schema.TableB
SELECT *
FROM my_schema.TableA
LOG ERRORS INTO my_schema.ERR$_TableA ('already exists') REJECT LIMIT UNLIMITED;
