Is it OK to swallow an exception for DB INSERT

Is it OK to swallow Duplicate key violation exceptions for INSERTS or should you check if the record exists?

So let’s say that I have a table Photo with one field: PhotoName. I’m looking through a file directory to add items to Photo. In the process, it’s possible that when I find a photoname, it might already be in the database. So there are two ways to go about this:

1) //Look to see if it exists before adding it. Only add it if it does not exist.

bool photoExists = SQLSELECTStatementToCheckIfThePhotoExists(photoName);
if(!photoExists)
  SQLCommandToInsertPhoto(photoName)

or 2) //Assume that it doesn't exist. If it does, catch and ignore.
try
{
  SQLCommandToInsertPhoto(photoName);
}
catch(DuplicateKeyException ex)
{
  //swallow it and continue on as if nothing happened.
}

On the one hand, I don’t necessarily like the notion of just “swallowing” an exception, but on the other hand, try…catch uses only one call to the DB. This happens to be in SQL Server.

Answer

You should definitely not just “swallow” the exception. You should be trying to find these duplicates and not insert them if needed.

On method could be checking where not exists on the key.

INSERT INTO TargetTable

SELECT 
    KeyID,
    blah,
    blerg,
FROM SourceTable AS S
WHERE NOT EXISTS (
    SELECT 1
    FROM TargetTable AS T
    WHERE S.KeyID = T.KeyID
    )

This method will allow you to only INSERT new rows into the table. This method of course does not account for any matching you may need to do for an UPDATE, however that’s outside the scope of this question but should still be thought about. Most users could also use MERGE I’ll post an example of that when I get time.