Using T-SQL MERGE command in SQL Server 2008

MERGE sql command is one of the new features introduced in upcoming SQL Server 2008. Basically, it’s used to insert, delete or update records in a target table basing on the result of join with the source table. Instead of using a combination of IFs and SELECTs MERGE makes it possible to write one query which will:
join the tables
specify matching values and perform requested action
specify non-matching values and also perform requested action
The following example should give you some idea about MERGE command.

I assume you have already created some database for testing purposes, so the first thing that needs to be done is preparing two tables. One of the tables, TestTable1, is going to be the target table, while the other one, SourceTable1, will be used as a source table for the merge. The following queries create the tables and populate then with some values. Notice the difference both in column naming and table definition.
CREATE TABLE TestTable1(tableId int PRIMARY KEY,textData varchar(20),intData int)
CREATE TABLE SourceTable1(tableId int PRIMARY KEY,someText varchar(20),someInt int,someBit bit)

INSERT INTO TestTable1 VALUES(1, ‘Test 1′, 21)
INSERT INTO TestTable1 VALUES(2, ‘Test 2′, 21)
INSERT INTO TestTable1 VALUES(7, ‘Test 7′, 21)
INSERT INTO TestTable1 VALUES(9, ‘Test 9′, 21)
INSERT INTO SourceTable1 VALUES(1, ‘Merge source 1′, 21, 0)
INSERT INTO SourceTable1 VALUES(2, ‘Merge source 2′, 31, 1)
INSERT INTO SourceTable1 VALUES(3, ‘Merge source 3′, 55, 1)
INSERT INTO SourceTable1 VALUES(4, ‘Merge source 4′, 1, 0)
INSERT INTO SourceTable1 VALUES(5, ‘Merge source 5′, 13, 0)
INSERT INTO SourceTable1 VALUES(6, ‘Merge source 6′, 90, 1)
INSERT INTO SourceTable1 VALUES(8, ‘Merge source 8′, 97, 1)
INSERT INTO SourceTable1 VALUES(9, ‘Merge source 9′, 6, 0)
INSERT INTO SourceTable1 VALUES(10, ‘Merge source 10′, 11, 0)

The tables are ready, so now it’s time to write the query to merge SourceTable1 into TestTable1 according to following rules:
1) If tableId of the source record does not exist in the target table, copy the record into target table skipping someBit value
2) If tableId of the source record exists in target table, overwrite target record’s textData with matching source record’s someText value. If source record’s someBit is set to 1 also overwrite intData with source’s someInt, otherwise leave it intact

The following query performs these actions:
MERGE INTO TestTable1 T
USING SourceTable1 S ON S.tableId = T.tableId
WHEN NOT MATCHED THEN INSERT (tableId, textData, intData) VALUES(S.tableId, S.someText, S.someInt)
WHEN MATCHED THEN UPDATE
SET textData = S.someText,
intData = CASE S.someBit WHEN 1 THEN S.someInt
ELSE T.intData
END;
As you can (hopefuly) see, the code is simple and easy to read. First, I defined the join criteria (ON S.tableId = T.tableId), then the INSERT action for non-matching records, and finally UPDATE action for matching records.
Warning: Notice the semicolon at the end of the statement - MERGE has to be terminated, else the execution will return an error.
Hint: You can use second WHEN MATCHED clause but you have to obey some rules. The first clause has to be accompanied by AND clause. The second WHEN MATCHED is applied if the first one isn’t - so you can not combine WHEN MATCHED clauses to perform more than one action on the same row. Also, when using two WHEN MATCHED clauses one of them has to specify UPDATE, and the other DELETE action.
Link: There is a pre-release documentation for MERGE which you can read here. If you are interested in more detailed description of the command it’s a good place to start.