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.The subquery could return multiple rows which will be inserted automatically into TAB1. I use this query to avoid using the cursor to fetch and insert for each record. So, I'm concerned about how Oracle reacts when it finds the duplicate. It will be perfect if it's able to ignore the duplicate records and just add in the new ones, then send an SQLCODE 'duplicate key' to notice that at least one row was found duplicate.
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);This will insert those rows from TAB2 that do not yet exist in TAB1.
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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.