How to Update If Row Exists Else Insert in SQL Server
Last Updated :
02 Feb, 2024
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 exampleExplanation: 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 existsExplanation: 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 outputEXPLANATION: 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 ... ELSEEXPLANATION: 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.
Similar Reads
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 ins
7 min read
SQLite â INSERT IF NOT EXISTS ELSE UPDATE
SQLite is a popular choice for embedded database applications due to its lightweight nature and ease of use. One common scenario in SQLite is the need to insert a row into a table if it does not already exist and update it if it does. In this article, We will explore how to achieve this efficiently
4 min read
How to Insert Row If Not Exists in SQL
Managing and manipulating data in SQL is essential, especially when it comes to avoiding duplicate entries in a database. Duplicate records can lead to data inconsistencies and errors that disrupt operations and analysis. The "INSERT IF NOT EXISTS" feature in SQL acts as a safeguard, ensuring that o
6 min read
How to INSERT If Row Does Not Exist in PL/SQL
The database management system (DBMS) stores, processes, and manipulates data in a database. All data in a database can be stored using the INSERT command or updated using the UPDATE command. These two data manipulation language (DML) commands play a key role in adding and maintaining the data. Some
7 min read
How to INSERT If Row Does Not Exist in MySQL
In databases, sometimes you want to add new information, but if they're already there, you might need to update their information. We use the term "upsert" for this dual action of inserting new records or updating existing ones. MySQL provides us with some simple ways to handle this. In this article
5 min read
How to Get the Identity of an Inserted Row in SQL Server
In SQL Server, we can use an Identity column, which will automatically increment the ID value in a table. The Identity column is a unique, sequential, and non-null value for each row in a table. The identity value is auto-incremented using the IDENTITY command. There could be situations when this au
4 min read
How to Perform Batch Updates in SQL Server
The Update statement is a SQL keyword to update data in the database. We can update all the rows in the database or some values with the help of conditions. The update is a SQL keyword, and it falls under Data Manipulation Language (DML), as the name suggests it is used to manipulate the data. Updat
5 min read
How to Update All Rows in SQL?
Updating records in an SQL database is a fundamental operation used to modify existing data. The UPDATE command is the go-to method for making such modifications, whether for all rows in a table or a subset based on specific conditions. In this article, we will explain how to update all rows in SQL
4 min read
How to Update Top 100 Records in SQL Server
SQL Server is a Relational database Management system which is developed by Microsoft and is one of the most used databases in the world. It provides the developer with a set of rich functionalities to create tables, insert data in them, and then manipulate and play with them as and when necessary.
5 min read
How to Get Latest Updated Records in SQL?
SQL is a flexible and widely used relational database management system in the software industry. Retrieving the latest updated records is a critical operation for data analysis, debugging, or monitoring changes in a database. In this article, we will explain various SQL techniques to get the latest
4 min read