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.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *