Simplifying updating data

A pattern I came across a few years ago for updating data is to use what is called an UpSert stored procedure. An UpSert stored procedure combines the insertion of new rows with updating them. Rather than have two stored procedures, one for inserting and one for updating, you simply have one that does both.

The benefits is that this leads to application code that doesn’t need to concern itself with determining whether a particular entity exists or not. Instead of writing code to determine whether a particular entity exists in the table or not, and then calling the insertor update stored procedure as appropriate, you simply invoke the UpSert stored procedure and let the UpSert stored procedure determine whether to insert or update the table.

Why write application code to do this, when your database can do this magnitudes of times faster. Here’s an example of how an UpSert stored procedure works.

Hide Expand

Copy Code

-- =============================================
-- Author:		Dominic Burford
-- Create date: 21/09/2017
-- Description:	Upsert a user
-- =============================================
CREATE PROCEDURE [dbo].[Users_Upsert] 
	@username VARCHAR(128),
	@email VARCHAR(128)
AS
BEGIN
 
    -- are we inserting a new record or updating an existing one?
	SELECT ID FROM	Users
		WHERE Email = @email
 
	IF @@ROWCOUNT = 0
		BEGIN
			INSERT INTO Users
			(
				UserName,
				Email
			)
			VALUES
			(
				@username,
				@email
			)
		END
	ELSE
		BEGIN
			UPDATE Users
				SET UserName = @username,
				Updated = GETDATE()
				WHERE Email = @email
 
		END
END
 
GO

This pattern also works well with RESTful APIs. Whenever you want to insert / update data, you don’t need to write code that determines if the entity exists and then invoke the appropriate POST or PUT method, your code will always be an HTTP POST. This leads to far cleaner and simplified code. It also works well with service bus architectures where you don’t care about the type of update you are performing, as it’s just a fire-and-forget call to the database.

The resulting code will also be quicker, as you have delegated the responsibility for determining if an entity exists or not to the database, which obviously can make such a judgement many times faster than your application code.

I use this pattern frequently throughout my applications, and particularly when designing and developing RESTful APIs. The pattern can be used in practically any application though, as I use the same pattern in web apps, mobile apps and console apps.