Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
 
What do you have? –  Colin 't Hart Mar 12 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? –  FrustratedWithFormsDesigner Mar 12 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. –  FrustratedWithFormsDesigner Mar 12 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 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/… –  Leigh Riffel Mar 12 at 16:01
show 2 more comments

1 Answer

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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