SQL Server 2008: Merge Statement

SQL Server 2008 introduced the Merge statement which is commonly also called as Upsert (Update / Insert). It can be used as Insert if the row does not exists and update, if it exists. It can be used as ETL process while fetching data. This feature is useful for developers. It is not required to give any particular join (inner, left outer etc).

Let’s see some advantages for using Merge statement. With Merge statement the ability to insert a row if it does not exist or to update if it exists, is provided with a single statement. With previous version of SQL it was required to create separate statements for insert, update or delete to be done. With Merge statement, a source table (or a query) is allowed to join with target table (or updatable view) based on a criteria. Depending upon the match of the criteria specified update, insert or delete can be used. There are 3 options in the condition WHEN MATCHED, WHEN TARGET NOT MATCHED, WHEN SOURCE NOT MATCHED.

We no longer see record for ID =3 as it gets deleted with this merge statement.

SummaryDo not forget to give ; (semi colon) at the end of Merge statement. You will end up in parser error otherwise. If multiple rows match in target for criteria following error is given.

Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

This is advantage of Merge over Update with join. Merge is deterministic whereas Update with join will update any row without any error.