If Row Exists Update, Else Insert in SQL Server

A user mailed me a block of C# code that updated a row if it existed and inserted, if the row was new. He wanted the same code to be done in SQL Server as well. If you too have a similar requirement, then here’s a sample query for you:

CREATE PROCEDURE usp_INSERTUPDATEEMP
(
@EmpID AS INT,
@LastName AS NVARCHAR(20),
@FirstName AS NVARCHAR(20)
)
AS
BEGIN
IF
EXISTS (SELECT EmployeeID FROM Employees WHERE EmployeeID = @EmpID)
BEGIN
-- Write your update query
UPDATE Employees
SET FirstName = @FirstName, LastName = @LastName
WHERE EmployeeID = @EmpID
END
ELSE
BEGIN
-- Write your insert query
INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES ( @EmpID, @FirstName, @LastName )
END
END

I am assuming here that there is a table called Employees which has three fields (EmployeeID, FirstName and LastName). If the EmployeeID already exists, then the First and LastName are updated. If there is a new record, it gets added to the table

Alternatively also check the MERGE statement which allows you to performs insert, update, or delete operations in a single statement.


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

4 comments:

Madhivanan said...

Other technique is


UPDATE.....

IF @@ROWCOUNT=0

INSERT.......

Anonymous said...

A good !.Thanks!!!

Anonymous said...

other faster technique is using MERGE Statement

Anonymous said...

This is not thread safe