Ever had the need to insert a record, but only wanted to do so if the record doesn't already exist? Typically the first approach to this is two queries: one to check for the existence of the values you want to insert, the second to peform that insert IF the first query returned no records. Well, I was in such a spot today, but hated the idea of having to hit the db twice to perform a conditional insert. Though I'm sure I'm not the first one to come up with this approach, I thought I'd share it in case it helps someone else, too.

I'm using a MySQL database, so the query I'm going to demonstrate is written a little bit MySQL-specific, though it is easily translated into MSSQL-speak as well.

The Scenario

Okay, we're looping through a file where each line represents a record. For each of those records, we want to perform an insert IF that record doesn't already exist. Here's how I'm doing it:

First of all, please know that I highly recommend the use of <cfqueryparam> tags in every place that you see a variable being output. I ommitted that tag in this example for clarity's sake.

Walking through the code, first thing I'm doing is grabbing my values from the current file line. Next, the query. It is obviously an INSERT query using a select statement. The select statement is simply selecting the actual raw values, but ONLY if a record meeting that exact criteria does not already exist. If no record is selected in the WHERE's subquery, the values are inserted; otherwise, nothing happens at all.

Notice my use of the "LIMIT" attribute. This performs the equivalent action of DISTINCT, ensuring that only a single record is returned. Without limiting the results of the SELECT statement, we would get multiple results and would be inserting massive amounts of duplicates. For MSSQL users, simply add the DISTINCT keyword to your SELECT statement, as in "SELECT DISTINCT '#carrier#', '#claimnum#','#priorclaim#' FROM ...."