I need to take some data from Table A, use some logic, and then insert one or more rows into Table B. I have a PLSQL block that brings in data from Table A with a cursor, performs all the logic required, and inserts the required rows.

The problem is there are duplicate rows in Table B - it's the nature of the beast. But I need the final result to be a Table B with no duplicates.

It's Oracle so temporary tables are bad form - what's the best way to accomplish this?

  • What do you have? Mar 12 '13 at 15:34
  • Are "duplicates" happening because there are no constraints preventing it, or are the rows are only considered as duplicates based on a subset of columns? Mar 12 '13 at 15:43
  • 1
    I'd have to see some actual code to know for sure, but it might be possible that BULK operations can help: akadia.com/services/ora_bulk_insert.html You might be able to use SELECT * BULK COLLECT to write a query that filters out duplicates... it might depend on the nature and structure of your data as well, but it might work. Mar 12 '13 at 15:45
  • 1
    How complicated is the logic? If you can fit it all into one query, you can just do a INSERT INTO MYTABLEB ( SELECT DISTINCT ...... FROM MYTABLEA )
    – Philᵀᴹ
    Mar 12 '13 at 15:52
  • 2
    If the duplicates are being introduced by the inserts from Table A, then it seems that a MERGE would be in order. docs.oracle.com/cd/E11882_01/server.112/e26088/… Mar 12 '13 at 16:01

If the duplicates are being introduced by the inserts from Table A, then it seems a MERGE would be in order. A MERGE can INSERT data when the record does not exist in Table B and UPDATE data if the record already exists thus preventing the introduction of duplicates.

FrustratedWithFormsDesign is correct that you probably should have a constraint preventing the introduction of duplicates. He is also correct in pointing out that this code would probably benefit from Bulk processing.

Phil is correct that if you only need to INSERT data, then a subquery pruning rows from the insert would be preferable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.