SQL Upsert

One of the first days at my first programming job, I was told to create and “Upsert Stored Procedure”. My first thoughts were, “Oh he had mistaken, it was insert he meant to say.” But no, Upsert is the correct term. An Upsert is simply an update or an insert, and as a shortcut, you can say “Upsert”.

So, you have a new record that needs to go into a Table. The first step is to check that table to see if that record already exists. If the record does exist, you can just update that record. If that record does not exist, you can insert a new one. This is a simple way to help prevent duplicates in your database.

In this example, I am going to demonstrate a simple Upsert via SQL Stored Procedure. First, we have a table, lets call it Customer.

In the Customer Table you can see we have several columns; Id, AccountNumber, FirstName, LastName and Dob. When creating out SQL Stored Procedure, we can use the AccountNumber as our where condition.

We need to pass in the information for each column as parameters for the SQL Stored Procedure.

Create PROCEDURE [stp_customer_upsert]
 (     
     -- Add the parameters for the stored procedure here 
         @AccountNumber nvarchar(50), 	
         @FirstName NVARCHAR(500), 	
         @LastName nvarchar(50), 	
         @Dob nvarchar(200) 
 )

Then we need to check to see if a Customer already exists with the Account Number that was passed in. If a record does exist, we will run a SQL Update where the Account Numbers match.

IF (EXISTS(SELECT(1) Id FROM Customer WHERE AccountNumber = @AccountNumber)) 	
    BEGIN 		
            UPDATE Customer  		
            SET 			
                    AccountNumber = @AccountNumber, 	             
                    FirstName = @FirstName, 
                    LastName = @LastName, 			
                    Dob = @Dob 		
            WHERE 			
            AccountNumber = @AccountNumber 	
     END

Otherwise, we will Insert that Customer as a new Customer with a SQL Insert.

ELSE     
    BEGIN         
        INSERT INTO Customer         
        (             
            AccountNumber, 	    
            FirstName, 	    
            LastName, 	    
            Dob         
        )         
        VALUES         
        (	        		
            @AccountNumber, 		
            @FirstName, 		
            @LastName, 		
            @Dob             
        )     
     END

And that’s it, it is that simple. Here is the entire SQL Stored Procedure.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [stp_customer_upsert]
(
    -- Add the parameters for the stored procedure here
	@AccountNumber nvarchar(50),
	@FirstName NVARCHAR(500),
	@LastName nvarchar(50),
	@Dob nvarchar(200)
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
	SET XACT_ABORT ON

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	BEGIN TRAN

    IF (EXISTS(SELECT(1) Id FROM Customer WHERE AccountNumber = @AccountNumber))
	BEGIN
		UPDATE Customer 
		SET
			AccountNumber = @AccountNumber,
			FirstName = @FirstName,
			LastName = @LastName,
			Dob = @Dob
		WHERE
			AccountNumber = @AccountNumber
	END
	ELSE
    BEGIN
        INSERT INTO Customer
        (
            AccountNumber,
	    FirstName,
	    LastName,
	    Dob
        )
        VALUES
        (	
       		@AccountNumber,
		@FirstName,
		@LastName,
		@Dob
            )
    END
	COMMIT
END