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.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: