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. Here is the list of topics that we are going to discuss.

• SQL Server stored procedure if exists update else insert (using Email)
• If exists update else insert SQL Server (using Email & Contact column)
• Stored procedure for insert and update in SQL Server (using Multiple Fields)
• Stored procedure for insert and update in SQL Server with output parameter

A SQL Server table is a common database object used to store data in an organized way. And many times, we may come across a situation where we are making entries into a table in SQL Server.

Now, as a best practice, these tables should not contain any duplicate values. But, while dealing with a huge amount of data, assuring unique values is a very difficult 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. And if the record does not exist then, it will insert the record in the table.

So, in this tutorial, we will understand how to create a stored procedure for if exists else 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 at any online website, we use our email address to create a profile. And we can only use one email id for one profile creation.
• So, in the first example, we will create a stored procedure in sql server that will check the existence of a profile.
• And if the profile already exists then, it will update the profile data using email, else it will insert the record in the profile table.

For this demonstration, we are using 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 upon email id. And it will run an update query if the email already exists in the table.

And 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 5 input parameters and first, we are checking the existence of the record using the email parameter. And based upon the existence, we are running the update or insert statement.

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

Let’s try to execute this procedure by providing an existing email id and update the country field. And 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, first, we are executing 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.

• To sign-up on such websites, it also uses contact numbers as a unique field.
• And 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 upon the existence of the record.

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

Now, we are going to create a stored procedure that will check the existence of a record based upon 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

In the above script, we have declared 6 input parameters and we are checking the existence of the record using the email and contact parameter. And based upon the existence, we are running the update or insert statement.

After the execution, the procedure will return a print statement based upon 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 are executing 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 are going to 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 upon 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 5 input parameters and we are checking the existence of the record using multiple parameters. And based upon 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 try 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 operation.

The OUTPUT parameter in a stored procedure is used to return data from the stored procedure to the caller or user. And 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 6 input parameters and 1 output parameter.
• In the body of the procedure, first, we are using the IF EXISTS statement to check the existence of the record based upon the values passed as input parameters. And perform insert or update operations based upon the existence of the record.
• Now, we are using the OUTPUT parameter to return a bit value based upon 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, for the execution of 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
• Full-text search in SQL Server
• Remote procedure call failed in SQL Server
• SQL Server view order by

So, in this tutorial, we learn about SQL Server stored procedure if exists update else insert operation. And we also demonstrated multiple examples for this task. Here is the list of topics that we have discussed.

• SQL Server stored procedure if exists update else insert (using Email)
• If exists update else insert SQL Server (using Email & Contact column)
• Stored procedure for insert and update in SQL Server (using Multiple Fields)
• Stored procedure for insert and update in SQL Server with output parameter