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;
Labels: t-sql programming