Ignore Duplicates in Oracle

When I wrote post about Ignore duplicates in SQL Server, I got the following comment from one of the LinkedIN group.

“

Also, Oracle has a great way to handle this in 10g and beyond.
By James Williams
@LinkedIN group SQL Server DBA

“

I thought looking into this with my very limited knowledge in Oracle.

I used Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 version.

First let me create the table with the unique constraints.

Let me insert three rows to the table.

Now the question is how oracle is managing duplicate inserts for unique constraints.

The above statement create an error logging table using the DBMS_ERRLOG package, and one of the inserts violates the check constraint on CITY, and that row can be seen in city_errors. If more than ten errors had occurred, the statement would have aborted, rolling back any insertions made:

Let’s say there is a another table of data (tempCity) and we want to populate CITY with tempCity.

If you simply insert tempCity into CITY entire transaction will be failed since Denver city already exists.

However, you can insert using following syntax.

So you can see only two rows will be inserted while other duplicate row is ignored.

by analyzing city_errors table you will be able to get which records were failed with the reason.