How to Update If Row Exists Else Insert in SQL Server

Data update and data insert are two important functions to add and update data in SQL Server Tables. Using SQL queries we can check for specific data if it exists in a table. The update query with the WHERE Clause can be used to update data passed from the front end or any other data generated from data processing during runtime. Sometimes it may be required to Insert the data if the same data is already present in the table.

In this article, we will look into the methods of updating data if already exists else insert the same data if the data does not exist, with examples.

Introduction to Insert or Update Data

Update data or Inserting data can be done in a single process without going back to the database from the back end of the application multiple times. So below are some of the ways we can update or insert data in a single process.

To update data, the data needs to be checked in the specific table if the data already exists. If the data already exists then it will be updated. If the data does not exist, then the same data will be inserted.

So all data required to insert data should be sent to the stored procedure or code block if the scenarios need to both check and update data or insert it.

Ways to Insert into a SQL Server table or update if exists

Create the Table Employees to test the examples:

Query:

CREATE TABLE [dbo].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [varchar](50) NULL,
    [Technology] [varchar](100) NULL
) ON [PRIMARY]
GO 

In Employees table below data already exists:

Employees table data

Method 1: UPDATE IF EXISTS ELSE UPDATE

Example 1: Update Data If Already Exist

Let’s Design a SQL script which updates the “Technology” information for an employee named ‘Raja Rajan‘ in the “Employees” table. The script should set the technology values to ‘Java, .NET, AZURE.’ Additionally, implement a conditional check using @@ROWCOUNT to ascertain whether any rows were affected by the UPDATE operation. If no rows are affected, insert a new record into the “Employees” table with the employee name ‘Raja Rajan‘ and the specified technology values of ‘Java, .NET, AZURE.’

Query:

UPDATE Employees Set Technology='Java, .NET, AZURE' where EmployeeName='Raja Rajan'
If(@@ROWCOUNT=0)
INSERT into Employees Values ('Raja Rajan','Java, .NET, AZURE')

Output:

Update example

Explanation: In the above example, the employees table has the employee ‘Raja Rajan’ and so the update query will work and the ‘Technology‘ column data will be updated. Before the update the Technolgy column for Employee ‘Raja rajan‘ had only ‘Java, .NET‘ and now ‘AZURE‘ will be added.

Example 2: Insert data if Data not Exist Already

Let’s Develop an SQL script that updates the “Technology” information for an employee named ‘Ramesh Kumar‘ in the “Employees” table. The script should set the technology values to ‘ASP.NET, .Net, C#, Xamarin.’ Include a conditional check using @@ROWCOUNT to determine whether any rows were affected by the UPDATE operation. If no rows are affected, insert a new record into the “Employees” table with the employee name ‘Ramesh Kumar‘ and the specified technology values of ‘ASP.NET, .Net, C#, Xamarin.’

Query:

UPDATE Employees Set Technology='ASP.NET,.Net,C#,Xmarin' where EmployeeName='Ramesh Kumar'
If(@@ROWCOUNT=0)
INSERT into Employees Values ('Ramesh Kumar','ASP.NET,.Net,C#,Xmarin')

Output:

Insert data if not exists

Explanation: In the above example the employee ‘Ramesh Kumar’ does not exist. And so the update query will return 0 rows updated as out put. And so the @ROWCOUTNT is 0, the Insert statement will work. This will insert the employee ‘Ramesh Kumar’ into the employees table.

Method 2: IF EXISTS and ELSE

Using the IF EXISTS … ELSE method we can update data if it already exists or insert data if it does not exist.

Example 1: Create Stored Procedure Used to Demo the Update or Insert with IF EXITS … ELSE

Let’s Develop a stored procedure named “Insert_Update_Data” that takes three parameters: @EmpID (Employee ID), @EmpName (Employee Name), and @Tech (Technology). The procedure is designed to update the “Technology” information for an employee with the specified ID (@EmpID) in the “Employees” table.

If the employee with the given ID exists, the procedure should perform an UPDATE operation on the “Technology” column for that employee. If the employee does not exist, the procedure should insert a new record into the “Employees” table with the provided employee name (@EmpName) and technology (@Tech).

Query:

CREATE Procedure Insert_Update_Data
@EmpID int, @EmpName varchar(100), @Tech varchar(100)
AS
BEGIN
IF EXISTS (SELECT 1 FROM Employees WHERE EmployeeID=@EmpID)
    BEGIN
        UPDATE Employees Set  Technology=@Tech where EmployeeID=@EmpID
    END
    ELSE
    BEGIN
        INSERT into Employees Values (@EmpName,@Tech)
    END
END

EXPLANATION: In the above stored procedure Insert_Update_Data, the IF EXISTS block with the subquery to check if a specific EmployeeID and if the employee exists already then the data is updated, but if there is No data , the INSERT query will work which is under the ELSE section.

Execute the Stored procedure using EXEC command with data as below:

To Insert New Employee

Query:

EXEC Insert_Update_Data 14,'Praskash Jain','React Native'

Output:

Insert data using stored procedure example output

EXPLANATION: In the above example using stored procedure, the stored procedure is executed using the EXE command with data for ‘Praskash Jain’ and EmployeeID 14. Since there is no employeeid 14 in the employees table, the Insert query will be executed and the data is inserted to the table.

To Update Existing Employee

Query:

Declare @EmpId int
Select @EmpId=EmployeeID from Employees where EmployeeName='Praskash Jain'
EXEC Insert_Update_Data  @EmpId,'Praskash Jain','React Native,Flutter,Sencha'

Below is how the Employees table will have the updated records:

Output:

Update Data using IF EXISTS … ELSE

EXPLANATION: In the above example the EmployeeID for ‘Praksah Jain‘ is retrived using the ‘Select‘ query and is stored in the variable @EmpId. The EmployeeID is then passed to the stored procedure with the employee details. Since this employee already exists, when we call the ‘Insert_Update_Data‘ stored procedure with the same employee which exists the Update Query will be executed. So now the same employee data will be updated with the new ‘Technolgy‘ data.

Using the IF EXISTS…ELSE method using the Stored procedure, if an existing data (here employee data) is passed, it checks for data and since it already exists it updates the additional data only.

Conclusion

In SQL Server, data insert and update are regular functions used to update a table with data. But care should be taken to check and verify a specific data if it already exists by using the key data like ID values in primary key or combination of data to check duplicate data and avoid updating multiple data records.

Checking only names or similar column value which could have duplicate value in a table may not be sufficient and the where clause should have the right combination of data to check existence of data in a table if only a single record in table need to be updated.