INSERTing duplicate key

[panayotisk]

This is a general question that applies to any DB I believe.
When do you check for a duplicate key?
Before INSERTing a new row do you issue a SELECT to see if the key already exists or do you let the INSERT fail and use @@ERROR to determine if the failure was due to a duplicate key violation?
In my SQL server DB I get better performance when I do the first. I was wondering what other people do in such a case.

[Klymer]

i go along with your preferred (first) method, and there is nothing wrong with that. in fact, it is the better method (in my eyes) if you want do do a insert or update - which i have to do often (insert record if it doesn't exist, otherwise update the record with that primary key)

[cjard]

it depends entirely on the design of the database and how you pick your unique IDs.

if youre adding users and the user name is unique, its ok to use error trapping and tell the person using the computer "that username already exists"

if youre writing a logging package and it is certain that "no more than one log event will be generated every second by each machine" then you can make your unique ID the dateTime + machine ID

if youre using an autonumber on some purchase order table, and the order number is a roling counter, then you just do: insert into tableX (select max(id_column)+ 1, 'abc' as valForCol2 ... from tableX)
the select query should contain all the data you want to insert..


there is no "one answer fits all" to this question, but error checking should always be presnet