ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

SQL Insert Or Update AKA Upsert

Categories: SQL Server

Since MsSQL 2008 you can use the MERGE command for inserting or updating, here is a small example I use with mojoPortal. I call it from a custom PageInitEventHandler and allows me to track what pages a user visits.

CREATE PROCEDURE [dbo].[kenTracking_Pages_Upsert]

@UserGuid	uniqueidentifier,
@SiteGuid	uniqueidentifier,
@PageGuid	uniqueidentifier

AS

SET NOCOUNT, XACT_ABORT ON;

MERGE [dbo].[kenTracking_Pages] WITH (HOLDLOCK) AS target
USING (SELECT @UserGuid, @SiteGuid, @PageGuid) AS source (User_Guid, Site_Guid, Page_Guid)
ON (target.User_Guid = source.User_Guid) AND (target.Site_Guid = source.Site_Guid) AND (target.Page_Guid = source.Page_Guid)
WHEN MATCHED THEN
UPDATE SET Visits = Visits + 1, DateModified = GETDATE()
WHEN NOT MATCHED THEN
INSERT
(
User_Guid,
Site_Guid,
Page_Guid
)
VALUES
(
@UserGuid,
@SiteGuid,
@PageGuid
);

RETURN @@ERROR;
GO