SQL Server 2008 Merger Statement

The most challenging issue of using SQL jobs to updating procedure is to check unique data with relative table. If you are using SQL Server 2005 or below you would have to write a series of T-SQL statements that check to see if the record exists, and then write an UPDATE or INSERT statement depending on your existence checks.

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). Here are a few facts that you must know before starting to use Merge Statement:

1. Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
2. Done as a set-based operation; more efficient than multiple separate operations
3. MERGE is defined by ANSI SQL; you will find it in other database platforms as well
4. Useful in both OLTP and Data Warehouse environments
   OLTP: merging recent information from external source
   DW: incremental updates of fact, slowly changing dimensions.
   

A typical merge statement looks like:

merge [dbo].[target] t
using [dbo2].[source] s on t.id = s.id
when not matched then
insert ([column1], [column2], [column3], [column4])
values s.[column1], s.[column2], s.[column3], s.[column4]