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?
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
brgdotnetcontractorAuthor Commented:
I am looking for a real world example?
slightwv (䄆 Netminder) Commented:
A real world example can mean a few different things.  I'm not going to create sample test cases for every possible variation I can think of.

I also don't know what programming language you might want an example in.

Put a primary key on the table and all you need is error handling to look for a unique constraint violation.  If you have a unique index it is a duplicate value error.

There are examples all over the web on these specific errors.

A error is:  ORA-00001
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Pallavi GodseSr. Digital Marketing ExecutiveCommented:
session 1:

create table t1 (pk int primary key, i int);
create table t11 (pk int primary key, i int);
insert into t1 values(1, 1);
insert into t11 values(2, 21);
insert into t11 values(3, 31);
commit;

Open in new window


session 2: insert into t1 values(2, 2);

session 1:

MERGE INTO t1 d
USING t11 s ON (d.pk = s.pk)
WHEN NOT MATCHED THEN INSERT (d.pk, d.i) VALUES (s.pk, s.i);

Open in new window

session 2: commit;

session 1: ORA-00001

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
@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.
awking00Information Technology SpecialistCommented:
>>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) Commented:
@brgdotnet,

Can I ask why you accepted a post that doesn't appear to help answer your question?
Pallavi GodseSr. Digital Marketing ExecutiveCommented:
@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) Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.