Does Oracle ignore duplicate rows found in subqueries?

My question is when I do an INSERT query having a subquery that collects data from another table, what happen if...

one of inserted records found has a duplicate in the inserted table? Will Oracle ignore the duplicate record and just insert the new ones, or stop the query and return SQLCODE as duplicate record? Example:

 INSERT INTO TAB1
    SELECT COLUMN1,
           COLUMN2,
           'N',
           COLUMN3
      FROM  TAB2
     WHERE  COLUMN1 = 'A'
 
 COLUMN1 and COLUMN2 is PK of TAB1.

If a duplicate row is found as determined by the Primary Key constraint, then the INSERT statement will fail and an error message will be displayed saying that the constraint has been violated. Since the INSERT statement fails, the entire body of work is rolled back. Therefore, no rows are inserted.

Oracle addressed this type of issue with the MERGE statement in their 9i release. If you are using Oracle 9i, then you might want to look at the MERGE statement.

If you are not using 9i, then a different way of performing the same thing is to use the MINUS or NOT IN operators to insert those rows from one table that are not found in the destination table. Taking your SQL statement one step further, it would look like this (let me assume COLUMN1 is the PK column):

INSERT INTO TAB1
   SELECT COLUMN1, COLUMN2, 'N', COLUMN3
   FROM  TAB2
   WHERE  TAB2.COLUMN1 NOT IN 
       (SELECT x.COLUMN1 FROM tab1 x);

For More Information

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.