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

Image for post
Image for post

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.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store