SQL Tip – Insert where not exists

Recently I had an issue where I needed to do a unique insert to a SQL database from a webapp. I could have easily done a select for the relevant data, compared it to my new data, and then do another query to the database to do my insert. Instead of doing all of comparison in code, I would much rather just have one SQL query that did it for me. By having just the one SQL query, I can keep the code clean, and reduce the number of calls to the database.

Sure enough, in SQL you can use the EXISTS keyword. EXISTS takes a subquery and returns a boolean that is TRUE if the provided subquery has any rows. Thus, we can effectively do our unique insert by checking for it’s existence in the table we are inserting into.

Example 1:

INSERT INTO dbo.table (field) 
SELECT 'field' 
WHERE NOT EXISTS 
    (SELECT field 
     FROM dbo.table 
     WHERE field = 'field')

Example 2:

INSERT INTO dbo.Customer (firstname, lastname, phone) 
SELECT 'Mitch', 'Valenta', '555-867-5309' 
WHERE NOT EXISTS 
    (SELECT firstname, lastname 
     FROM dbo.Customer 
     WHERE firstname = 'Mitch' AND lastname = 'Valenta')

Breaking down example 2, you can see that the subquery is checking to see if there isn’t a record in the customer table for Mitch Valenta. If none are found, the main query select actually has values and gets inserted into the Customer table. Without using the EXISTS, I would have to make two separate calls to the database, one for the subquery and one for the main query.

More examples and information can be found here