SQL Server stored procedure if exists update else insert

In this SQL Server tutorial, we will learn about SQL Server stored procedure if exists update else insert. And we will also try to implement some examples related to it.

SQL Server stored procedure if exists update else insert

A SQL Server table is a shared database object used to store data organizationally. We may often encounter a situation where we make entries in a table in SQL Server.

As a best practice, these tables should not contain duplicate values. But, while dealing with a huge amount of data, assuring unique values is a challenging task.

One solution to overcome this issue is to create a stored procedure in SQL Server that checks the existence of a record in the table. If that record already exists, then the stored procedure will update that record. The record will be inserted in the table if it does not exist.

So, in this tutorial, we will understand how to create a stored procedure for if there exists an update operation in SQL Server 2019. And we will also try to demonstrate multiple examples.

SQL Server stored procedure if exists update else insert (using Email)

Let’s start with a basic example related to profile data.

• Generally, when we make a profile on any online website, we use our email address to create a profile. We can only use one email ID for one profile creation.
• So, in the first example, we will create a stored procedure in the SQL server that checks the existence of a profile.
• If the profile already exists, then it will update the profile data using email; otherwise, it will insert the record in the profile table.

For this demonstration, we use the following profile table, as shown in the image below.

Now, we are going to create a SQL stored procedure that will check the existence of a record based on email ID. And it will run an update query if the email exists in the table.

If the email is unique, then it will run an insert query to add the record to the table. The complete script of the stored procedure is given below.

CREATE PROCEDURE usp_InsertProfileData
(
    @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @Email VARCHAR(50),
    @Gender VARCHAR(50),
    @Country VARCHAR(50)
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM [PROFILE_DATA]
    WHERE Email=@Email
	)
    BEGIN
        UPDATE [PROFILE_DATA] SET 
	       first_name = @first_name,
	       last_name = @last_name,
	       Gender = @Gender,
               Country = @Country
	       WHERE Email = @Email
	PRINT 'Profile Data Updated'
    END
ELSE
    BEGIN
        INSERT INTO [PROFILE_DATA] VALUES (@first_name, @last_name, @Email, @Gender, @Country)
	PRINT 'New Profile Data Inserted'
    END
END

In the above procedure, we have declared five input parameters, and first, we check the existence of the record using the email parameter. Based on this fact, we are running the update or insert statement.

The procedure will return a print statement based on the action performed by the stored procedure.

Let’s try to execute this procedure by providing an existing email id and updating the country field. Also, try to execute the procedure by providing a new record.

USE [sqlserverguides]
GO

EXEC [usp_InsertProfileData] 'Lynette', 'Skehan',	'lskehan0@unicef.org', 'Male', 'New Zealand'

EXEC [usp_InsertProfileData] 'Orel', 'Yukhnevich', 'oyukhnevich9@a8.net', 'Female', 'United Kingdom'

In the above example, we first execute the procedure by providing an existing email and changing the country field from “United States” to “New Zealand“.

After this, we are executing the procedure by providing new values. In the end, we will get the following output.

Read: SQL Server stored procedure insert into with examples

If exists update else insert sql server (using Email & Contact column)

In the previous section, we understand how to create a stored procedure to check an existing record using an email field in SQL server 2019. Now, let’s take the example of social media websites and applications.

• It also uses contact numbers as a unique field to sign up on such websites.
• This contact number can also represent the unique record in the table.
• Now, let’s create a stored procedure that checks the existence of a record using email and contact fields.
• And perform the update or insert operation based on the existence of the record.

For this demonstration, we are using the following profile table, as shown in the screenshot below.

Now, we will create a stored procedure that will check the existence of a record based on email and contact number. The complete script of the stored procedure is given below.

CREATE PROCEDURE usp_InsertProfileData
(
    @first_name VARCHAR(50),
	@last_name VARCHAR(50),
	@Email VARCHAR(50),
	@Gender VARCHAR(50),
	@Country VARCHAR(50),
	@Contact VARCHAR(50)
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM [PROFILE_DATA]
    WHERE Email=@Email
    and Contact=@Contact
	)
    BEGIN
        UPDATE [PROFILE_DATA] SET 
		first_name = @first_name,
		last_name = @last_name,
		Gender = @Gender,
		Country = @Country
		WHERE Email = @Email
		and Contact = @Contact
	PRINT 'Profile Data Updated'
    END
ELSE
    BEGIN
        INSERT INTO [PROFILE_DATA] VALUES (@first_name, @last_name, @Email, @Gender, @Country, @Contact)
	PRINT 'New Profile Data Inserted'
    END
END

We have declared six input parameters in the above script and are checking the record’s existence using the email and contact parameters. Based on this fact, we are running the update or insert statement.

After the execution, the procedure will return a print statement based on the action performed by the stored procedure.

Let’s try to execute this procedure by providing an existing email id and contact number. And try to modify the country name for that record.

USE [sqlserverguides]
GO

EXEC [usp_InsertProfileData] 'Hedy', 'Ingall', 'hingall0@blogs.com', 'Male', 'Australia', '386-379-8899'

In the above example, we execute the procedure by providing existing email and contact values. And we are trying to modify the country field from “United States” to “Australia“.

Read: How to execute stored procedure in SQL Server

Stored procedure for insert and update in sql server (using Multiple Fields)

Till now, we have discussed how to create a stored procedure to check the existence of a record using one or 2 fields.

Now, we will learn to create a stored procedure using multiple fields to check the existence of a record and perform insert or update operations.

For this demonstration, we will use the following car inventory table that contains information related to old cars and their price.

Now, we will try to create a stored procedure that will update the price of the car based on the existence in the table. For this implementation, we have created the following stored procedure.

CREATE PROCEDURE InsertCarInventory
(
    @Brand VARCHAR(50),
    @Model VARCHAR(50),
    @Year VARCHAR(50),
    @VIN VARCHAR(50),
    @Price VARCHAR(50)
    
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM [Car_Inventory]
        WHERE Brand=@Brand
            and Model=@Model
            and Year=@Year
	    and [VIN No]=@VIN   
    )
    BEGIN
        UPDATE [Car_Inventory] SET Price=@Price
        WHERE Brand=@Brand 
            and Model=@Model 
            and Year=@Year
	    and [VIN No]=@VIN
        PRINT 'Car Price Updated'        
    END
ELSE
    BEGIN
        INSERT INTO [Car_Inventory] VALUES (@Brand, @Model, @Year, @VIN, @Price)
        PRINT 'New Record Inserted'
    END
END

In the above script, we have declared five input parameters and are checking the record’s existence using multiple parameters. And based on the existence, we are running the update or insert statement.

After the execution, the SQL procedure will either update the price of the car or insert the record in the table.

Let’s try to execute this procedure by providing existing values and trying to modify the price of a car.

USE [sqlserverguides]
GO

EXEC InsertCarInventory 'Cadillac', 'DeVille', '1994', 
                        'WAUJC68E94A467431', '$10000'

Now, if we execute the above query, the price of the given car will be modified.

Read: Loop in SQL Server stored procedure

Stored procedure for insert and update in SQL Server with output parameter

Till now, we were only using input parameters in the stored procedure for the implementation. Now, let’s see how we can use output parameters in the stored procedure for insert and update operations.

The OUTPUT parameter in a stored procedure returns data from the stored procedure to the caller or user. In this case, we can use this output parameter to determine whether an insert or update statement is executed.

Let’s understand the usage of the OUTPUT parameter with the help of an example. For this, we have created the following stored procedure.

USE [sqlserverguides]
GO

/****** Object:  StoredProcedure [dbo].[usp_InsertProfileData]    Script Date: 01-09-2021 10:56:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_InsertProfileData]
(
    @first_name VARCHAR(50),
    @last_name VARCHAR(50),
    @Email VARCHAR(50),
    @Gender VARCHAR(50),
    @Country VARCHAR(50),
    @Contact VARCHAR(50),
    @flag BIT OUTPUT
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM [PROFILE_DATA]
    WHERE Email=@Email
    and Contact=@Contact
	)
    BEGIN
        UPDATE [PROFILE_DATA] SET 
		first_name = @first_name,
		last_name = @last_name,
		Gender = @Gender,
		Country = @Country
		WHERE Email = @Email
		and Contact = @Contact
	    SET @flag = 0           --return 0 for successfull update in the table
    END
ELSE
    BEGIN
        INSERT INTO [PROFILE_DATA] VALUES (@first_name, @last_name, @Email, @Gender, @Country, @Contact)
	    SET @flag = 1          --return 1 for successfull insert in the table
    END
END
GO

• In the above example, we have created a stored procedure with six input parameters and one output parameter.
• In the procedure’s body, first, we use the IF EXISTS statement to check the existence of the record based on the values passed as input parameters. And perform insert or update operations based on the existence of the record.
• Now, we are using the OUTPUT parameter to return a bit value based on the statement executed.
• If the update statement is executed, then the parameter will return 0, and if the insert statement is executed, then the parameter will return 1.

Now, to execute this procedure, we are going to run the following script.

USE [sqlserverguides]
GO

DECLARE @flag BIT

EXEC [dbo].[usp_InsertProfileData] 'Hedy', 'Ingall', 'hingall0@blogs.com', 
                                   'Male', 'Australia', '386-379-8899', @flag OUTPUT
if @flag=1
 print 'Successfully inserted new record'
else
 print 'Successfully updated the record'

In the above script, first, we have declared a variable that can store the value of the output parameter. And pass the variable as a value for the output parameter. After execution, we will use the variable value to determine which statement is executed by the stored procedure. Here is the execution of the above example.

You may also like reading the following articles.

• SQL Server stored procedure modified date
• Stored procedure for search functionality in SQL Server
• Try catch in SQL Server stored procedure
• SQL Server find text in stored procedure
• SQL Server stored procedure parameters

So, in this tutorial, we learn about SQL Server stored procedure if exists update else insert operation. We also demonstrated multiple examples for this task.