Upsert Tip - solving problems with MERGE Statement in SQL Server

MERGE is one of the nice statements SQL Server has introduced for enabling the upsert functionality. Upsert means Insert if record does not exist or Update if it is already present. This tip gives a short solution of one such problem where records won't get inserted or updated.

A simple syntax of the above statement is

MERGE INTO [YourTableName] as tablealias
using (Select
FieldValue1 as Field1,
FieldValue2 as Field2,
KeyValue as KeyV
) as mynewrecord
on mynewrecord.KeyV  = tablealias.KeyV
WHEN MATCHED THEN
update set
Field1     = @fieldnewvalue1,
Field2 = @fieldnewvalue2
WHEN NOT MATCHED THEN
INSERT  (
Field1,
Field2,
KeyV
)
VALUES
(
@fieldnewvalue1,
@fieldnewvalue2,
@NewKeyValue
);
END

The above statement can be easily made to be a part of a stored procedure which can be invoked for upsert. A very important point to note here is to make sure that the key we use for checking if a record exists or not needs to be unique.

The reason for putting up this tip is an error which occured to me because of a non-unique key. The new record was not being inserted into the table while doing the UPSERT using the MERGE statement. I used a stored procedure. After a few minutes of debugging we found that the issue was due to the uniqueness of the key being used. Infact my table needed a composite key. So after using all the fields in the condition, the issues were resolved.