Performing UPSERT in T-SQL

Very often there is the need to check if a key value exists to perform an update, and if it does not exist to insert new data. The upcoming SQL Server 2008 provides the MERGE statement (MERGE actually allows to do more: simultaneous UPDATE, INSERT and/or DELETE operations on the table), but until it is released we have to wait.

Here is just one way to implement in the current versions of T-SQL.

CREATE TABLE Foo (
 keycol INT PRIMARY KEY,
 datacol CHAR(1) NOT NULL);
 
-- Sample data
INSERT INTO Foo VALUES (1, 'a');
INSERT INTO Foo VALUES (2, 'b');
INSERT INTO Foo VALUES (4, 'd');
 
-- New values to insert/update
DECLARE @key INT;
DECLARE @data CHAR(1);
 
-- New key, will perform insert
SET @key = 3;
SET @data = 'c';
 
BEGIN TRAN
 
-- Try update
UPDATE Foo WITH (SERIALIZABLE)
SET datacol = @data
WHERE keycol = @key;
 
-- If no rows updated then must be new value, perform insert
IF @@ROWCOUNT = 0 
INSERT INTO Foo VALUES (@key, @data);
 
COMMIT
 
-- Existing key, will perform update
SET @key = 4;
SET @data = 'x';
 
BEGIN TRAN
 
-- Try update
UPDATE Foo WITH (SERIALIZABLE)
SET datacol = @data
WHERE keycol = @key;
 
-- If no rows updated then must be new value, perform insert
IF @@ROWCOUNT = 0 
INSERT INTO Foo VALUES (@key, @data);
 
COMMIT
 
SELECT keycol, datacol 
FROM Foo;

The SERIALIZABLE hint here is very important to avoid deadlocks.