It's easy to do a conditional insert using the server language of your choice, but to do it with SQL code exclusively is not as easy. There are some language additions and non-standard SQL code in most of the popular database servers, but a conditional insert can be done using straight SQL making it platform independent. This is useful in writing SQL scripts to perform database updates. Running a script without a conditional insert can create problems if the script is run twice, for example. This article will show the technique I use in doing a conditional insert using MS Access, MS SQL Server, and MySQL. The examples will use the Northwind database sample, available for Access and MS SQL out of the box and for MySQL using a script downloadable from my site. Scripts can be run from the database administrative interface of your choice.
A typical insert in a script might look like this:
INSERT INTO Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
VALUES
('Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0)
This script will insert one row into the Products table. What happens if the script is run twice, though? You will end up with 2 identical products in the database. A script like this might be passed off to your users to implement. If this is part of a larger script and there are any errors whatsoever in the script, the user might try to run it again, creating two inserts of the same product data. What we need is a way to limit this to one time.
A SELECT statement can be written to return static data -- not from any table:
SELECT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
Notice that there is no FROM clause in this statement. The SELECT statement simply returns the data. This will return a resultset using the same data that was used in the INSERT statement above.
An INSERT can use a SELECT statement as a way to supply the data for insertion. This statement is valid:
INSERT INTO Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
The fields are in the same order, so the data is inserted as you would expect. What we need is a WHERE clause to limit the insert to only inserting the data if the data doesn't exist. We can't do this, however:
INSERT INTO Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
WHERE 'Some New Product' NOT IN (SELECT ProductName FROM Products)
Because there is no FROM clause, the WHERE clause creates a syntax error. You can, however, select static data from a table, even though no data is returned. This is legal syntax:
SELECT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
FROM Products
This will return the same data, however it is selecting from the Products table. No data from the actual table is returned, however all rows of the table are returned because there is no limit. In other words, this static data will be returned once for every row in the table. We can use a DISTINCT clause to avoid this:
SELECT DISTINCT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
FROM Products
This returns the one row we want. Try it with the WHERE clause that seems to make sense, however, and you'll see that it doesn't work:
SELECT DISTINCT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
FROM Products
WHERE 'Some New Product' NOT IN (SELECT ProductName FROM Products)
This will return one row regardless of whether the data exists already or not. We need a WHERE clause that will return 0 rows if the data already exists. The following statement will return a 1 if the data exists and a 0 if the data doesn't exist:
SELECT COUNT(*) FROM Products
WHERE ProductName <> 'Some New Product'
Now all we need is a way to filter the original insert by this number. We can use the number 1 to compare in our WHERE clause, creating an INSERT statement that works the way we want it to by inserting a new row if the data doesn't exist and not adding the data if it already exists:
INSERT INTO Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT DISTINCT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
FROM Products
WHERE 1 >
(SELECT COUNT(*) FROM Products WHERE ProductName <> 'Some New Product')
The DISTINCT clause is not the most efficient way to accomplish this insert if there is a lot of data, so the following statements will create the same result using database-specific syntax:
INSERT INTO Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT TOP 1 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
FROM Products
WHERE 1 >
(SELECT COUNT(*) FROM Products WHERE ProductName <> 'Some New Product')
INSERT IGNORE INTO Products
(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
SELECT 'Some New Product', 12, 1, 1, 9.99, 1000, 0, 100, 0
FROM Products
WHERE NOT EXISTS
(SELECT 1 FROM Products WHERE ProductName = 'Some New Product') LIMIT 1
With a little creative SQL, you can tackle most problems in the database at the database level.