SQL 2008 - MERGE is Back

So what's is the MERGE statement all about? Well basically it's doing an INSERT, UPDATE and DELETE in a single statement. It used to be part of the early betas of SQL Server 2005 but didn't make in the RTM version. And now it's back :-)

Here's a little example:

MERGE Departments AS d
USING Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN SOURCE NOT MATCHED THEN
DELETE;

Departments_delta contains updated Department information. Based on the primary key we decide wether to insert, delete, or update the Department table.