Tuesday, June 24, 2014

Single record UPSERT in MSSQL 2008+

I guess I just don't use SQL's MERGE statement enough to remember its syntax off the top of my head. When I look at the Microsoft documentation for it, I can't find just a simple example to do a single record UPSERT. When I google, I don't get any brief blog posts that show it. So here's a brief one to add to the pile:

Table:

DATA_VERSION
datakey varchar(16) not null primary key,
seqno bigint not null,
last_updated datetime not null

The table just keeps track of sequence numbers for things identified by 'datakey'.  The first time we want to increment a value for a key we need to insert a new record with starting with seqno = 1 then every time after that we just want to increment the existing row.

MSSQL: