asked on

Best way to insert if record does not exists?

I need to check if one instance of a record exists in a table before inserting the records. As I don't want to insert the record twice. I need an Oracle expert to show me an example of how best to do that?

slightwv (䄆 Netminder)

If you have a primary key on the table then you cannot insert a duplicate. Another alternative is a unique index. It is similar to a primary key except it will allow a null value in the fields.

Worst case: Select from the table first and see if you get rows. If not, then insert.

If you can provide a little more information showing what you are trying to achieve, we can probably provide more information.

brgdotnet

ASKER

I am looking for a real world example?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Pallavi Godse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

@Pallavi,

I believe the question involves a single table. MERGE isn't really set up to work on a single table. You can get it to work but I'm not sure it is the best way to answer this question.

awking00

>>I am looking for a real world example? <<
Show us some sample data that exists in your world with what you expect the final result to be and it will help us better understand your need.

slightwv (䄆 Netminder)

@brgdotnet,

Can I ask why you accepted a post that doesn't appear to help answer your question?

Pallavi Godse

@slightwv (䄆 Netminder)

I think @brgdotnet might have tried the solution and it might be working for him. Therefore, he has accepted the solution. If you have some better solution than this, you can go forward and suggest,

slightwv (䄆 Netminder)

I always question when ALL participants get accepted/assisted. If MERGE worked and answered the question then I shouldn't get points. If it was a unique constraint/primary key then you shouldn't.

I don't see splitting points in this question.