Twitter Updates

    follow me on Twitter

    Saturday, June 09, 2007

    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.

    No comments: