How to Insert If Not Exists in SQL SERVER?

Adding Data to a table in SQL Server is a key operation. Data can be inserted into tables using many different scenarios like plain data inserted into a table without checking anything or checking if data already exists in the target table and only if the data does not exist then the new data is inserted. There are many methods to check the data if it exists like IF EXISTS, IF NOT EXISTS, or using the WHERE clause.

In this article, we will discuss the ‘IF NOT EXISTS’ alternative to check and insert a row of data into a table or to bulk insert data into a table.

Introduction to IF NOT EXISTS in SQL SERVER

IF NOT EXISTS is a keyword to check for the existence of a specific record in a table with the condition set in the where clause of the query used inside this function. This keyword is used to check for specific data if it exists and if not exist the same data can be inserted in the same table. So this keyword ‘If Not Exist’ is helpful to avoid duplication of data in a table and insert unique data only.

There are 3 methods through which we can Insert data using the NOT EXISTS method. Below are the 3 approaches available in SQL Server.

1. IF NOT EXISTS then INSERT.
2. INSERT … Where NOT EXISTS.
3. Using MERGE … INSERT.

We will see in detail these 3 approaches to Inserting data into a table when the data does not exist already.

Ways to Insert If Not Exists in SQL SERVER

Method 1: IF NOT EXISTS then INSERT

Syntax:

IF NOT EXISTS (Condition with Subquery)
BEGIN
<Insert Query>
END

Explanation: IF NOT EXISTS is the keyword to check the existence of data and the condition with subquery is the SELECT query with WHERE clause to check the data.

If no data exists for the condition provided in the WHERE clause of the subquery, then data is inserted into the table using the INSERT Query.

Let’s create an table named ‘Products‘ is used in the below examples with the create table query, table Products_Copy also has the same structure as below.

Query:

CREATE TABLE [dbo].[Products]
(
    [ProductID] [int] NOT NULL IDENTITY(1,1),
    [ProductName] [varchar](100) NULL,
    [SupplierID] [tinyint] NULL,
    [CategoryID] [tinyint] NULL,
    [Unit] [varchar](100) NULL,
    [Price] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

Output:

Inital data in Products and Products_Copy Tables

Explanation: Inital data in the Products Table and Products_Copy tables which are used in the examples below:

Example 1: Simple Example of IF NOT EXISTS Method

Let’s Create a script that ensures the uniqueness of the product name ‘Jeera Rice‘ in the “Productsnew” table. If there is no existing entry for ‘Jeera Rice’, then insert a new record into the table as given in the Query.

Query:

IF NOT EXISTS(Select ProductName from Productsnew where ProductName='Jeera Rice')
BEGIN
INSERT INTO Productsnew (ProductName,SupplierID,CategoryID,Unit,Price) Values ('Jeera Rice',1,7,'7,5 kg',120)
END

Output:

Products tableI F NOT EXISTS – Example 1

Explanation: In this example the product name is checked in the Products table using the where clause condition ProductName=’Jeera Rice‘, and since this product does not exists, the data is inserted to the Products Table.

Example 2: Using a Stored Procedure

Let’s Design a stored procedure named InsertProducts that takes parameters for product details – @ProdName (Product Name), @CatID (Category ID), @Units (Unit), and @Price (Price). The procedure should check if a product with the same name (@ProdName) already exists in the “Products” table. If it doesn’t exist, the procedure should insert a new record into the “Products” table with the provided details.

Query:

CREATE PROCEDURE InsertProducts
@ProdName varchar(100),
@CatID int,
@Units  varchar(100),
@Price Decimal
AS
BEGIN
IF NOT EXISTS(Select ProductName from Products where ProductName=@ProdName)
BEGIN
    INSERT INTO Products (ProductName,CategoryID,Unit,Price) Values (@ProdName,@CatID,@Units,@Price)
END
END

Explanation: Call the stored procedure with data to check and insert as below.

Query:

EXEC InsertProducts 'Oats Quaker',2,8,'1 kg',490

Updated Products Table after inserting data from the above 2 Inserts.

Output:

Products table IF NOT EXISTS – Example 2

Explanation: Since there is no data present in Products table for ‘Oats Quaker’, this data will be inserted to the table. From the above output of data in Products table, we can see that the two records from the above 2 examples are inserted after checking if same data already exists in the table. Data with product names ‘Jeera Rice’ and ‘Oats Quaker‘ are the two new records added to Products table as shown above.

Method 2: INSERT … WHERE NOT EXISTS

When we need to insert data into a table from another table to create a copy or back up purpose or to insert one or two columns of data into a table from another table, we can use the ‘NOT EXISTS’ method to insert data. A simple example is as below:

Syntax:

INSERT INTO <Table Name> (<Columns List>) 
<Select Query>
WHERE NOT EXISTS (Sub Query)

Explanation: In this example 2 tables are used. The Products table and Products_Copy table, which has the same table structure as the Products table. Products_Copy also has the first 9 records.

Example 1: Inserts Records for Those ProductID Does Not Already Exist in the Products_Copy Table

Let’s Write a SQL query that inserts records from the “Products” table into the “Products_Copy” table, but only for those products whose ProductID does not already exist in the “Products_Copy” table. This ensures that only unique products are copied to the “Products_Copy” table, avoiding duplication of data.

Query:

INSERT INTO Products_Copy (ProductID,ProductName,SupplierID,CategoryID,Unit,Price)
SELECT ProductID,ProductName,SupplierID,CategoryID,Unit,Price from Products
WHERE NOT EXISTS (SELECT ProductName FROM Products_Copy WHERE Products_Copy.ProductID = Products.ProductID)

Output:

Product_Copy table Where NOT EXISTS

Explanation: In this above example the WHERE clause in select query for Products table check for all data which do not exists in Products_Copy table. If any data is missing, the same data will be inserted into the Products_Copy table.

See below the data after bulk insert in the Product_Copy Table with data from the example queries executed in Products Table. There are two records added from Products table.

In the above example for bulk insert, the newly inserted rows in Products table for product names ‘Jeera Rice’ and ‘Oats Quaker‘ are copied and inserted to the Products_Copy table. This will be helpful for taking a backup of data inserted to the backup table at intervals or at a specific time when database user access is minimal, like during the night.

Method 3: Using MERGE … INSERT

The MERGE method is another method to Insert, update or delete operations on a target table based on the result of JOIN condition with the source table.

Syntax:

Merge  <Target Table>
Using <Source Table>
ON <key field to join>
WHEN MATCHED THEN
<Update Query>
WHEN NOT MATCHED THEN
INSERT (<Columns List - Target Table>) Values (<Columns List - Source Table>)

Two additional rows are added to the Products table to demo the below example.

Query:

 INSERT INTO Products (ProductName,SupplierID,CategoryID,Unit,Price) Values ('Matta Rice',1,7,'5 kg',275)
 INSERT INTO Products (ProductName,SupplierID,CategoryID,Unit,Price) Values ('Briyani Rice - India Gate',1,7,'5 kg',600)

Explanation: Two additional records are added to Products table when we execute the above code.

Example 1: Merge Data for Those Products Whose ProductID Does Not Already Exist in the “Products_Copy” Table

Let’s Implement a SQL query using the MERGE statement to merge data from the “Products” table into the “Products_Copy” table. The merge should insert records into “Products_Copy” for those products whose ProductID does not already exist in the “Products_Copy” table, ensuring that unique products are copied. The merge operation checks for matches based on ProductID and inserts new records when there is no match in the target table

Query:

MERGE Products_Copy AS ProdC
USING Products AS Prod
ON ProdC.ProductID = Prod.ProductID
WHEN NOT MATCHED THEN
 INSERT (ProductID,ProductName,SupplierID,CategoryID,Unit,Price) VALUES 
(ProductID,ProductName,SupplierID,CategoryID,Unit,Price); 

Output:

Products_Copy Table – Merge

Explanation: In Merge command the Target table is given to the MERGE and Source table is given to the USING table name. The ‘WHEN NOT MATCHED’ condition is used to check the records which are not matched between both Source and Target tables. The unmatched data from Source to Target is copied.

From the above output, we see that the 2 records (Record IDs 12 and 13) added are merged to products_copy table from Products table using MERGE method.

Conclusion

Inserting data after checking if data exists is a key method to insert data and avoid duplicate data in tables. The ‘IF NOT EXISTS’ is one method to insert data after checking if a specific record already exists in a table. Also, when we need to copy and insert data from one table to another table by checking the existing data using NOT EXISTS option in the ‘WHERE’ clause of the SELECT query, which will check and avoid any duplicate data when transferring data from one table to another.