Insert VS (Select and Insert)

I am writing a simple program to insert rows into a table.But when i started writing the program i got a doubt. In my program i will get duplicate input some times. That time i have to notify the user that this already exists.

Which of the Following Approaches is good to Use to achieve this

1. Directly Perform Insert statement will get the primary key violation error if it is duplicate notify otherwise it will be inserted. One Query to Perform

2. First make a s开发者_开发知识库earch for the primary key values. If found a Value Prompt User. Otherwise perform insert operation.For a non-duplicate row this approach takes 2 queries.

Please let me know trade-offs between these approaches. Which one is best to follow ?

Regards, Sunny.

I would choose the 2nd approach.

The first one would cause an exception to be thrown which is known to be very expensive...

The 2nd approach would use a SELECT count(*) FROM mytable WHERE key = userinput which will be very fast and the INSERT statement for which you can use the same DB connection object (assuming OO ;) ).

Using prepared statements will pre-optimize the queries and I think that will make the 2nd approach much better and mre flexible than the first one.

EDIT: depending on your DBMS you can also use a if not exists clause

EDIT2: I think Java would throw a SQLExcpetion no matter what went wrong, i.e. using the 1st approach you wouldn't be able to differ between a duplicate entry or an unavailable database without having to parse the error message - which is again a point for using SELECT+INSERT (or if not exists)