Merge Statement in MSSQL

Merge statement introduced in sql server 2008 allows us to perform insert, update & delete in one statement. We no longer have to use multiple statement for performing insert/update/delete.

With merge statement we requires 2 tables
    i) Source table - contain changes that needs to be applied to the source table.
    ii) Target table - table that requires changes (insert/update/delete)

    Merge statement joins the target table to source table by using common column in both the tables. Based on how the rows matchup. We can perform insert/update/delete on target table.

    Syntax:
    Merge [target]  as t
    Using [source] as s
    On [join condition]
    When matched then [updated statement]
    When not matched by target then [ insert statement]
    When not matched by source then [delete statement];

    Last statement should end with semi colon (;)

    E.g.:
    Merge into studenttarget as t
    Using studentsource as s
    On t.id =s.id
    When matched then update set t.name = s.name
    When not matched by target then insert (id,name) values (s.id,s.name)
    When not matched by source then delete;

    If delete is not required in above condition then we can omit the entire case.

    MERGE INTO dbo.PROJECTS_TESTS AS ps
USING #temp_Test AS tC
  ON ps.PROJECTID = tC.projectId   AND ps.ANOTHERTID = tC.anotherLink   AND ps.DOMAINID = tC.DOMAINID   AND ps.is_test = tC.test_project
WHEN NOT MATCHED
THEN INSERT ( PROJECTID, ANOTHERTID, DOMAINID, is_test)
VALUES ( tC.PROJECTID, tC.ANOTHERTID, tC.DOMAINID, tC.test_project)

    Example : (Only update & insert rows that changed? Try using EXISTS and EXCEPT with merge)
    MERGE INTO #Customer c
USING #Updates u ON u.CustomerID = c.CustomerID
WHEN MATCHED AND EXISTS (
                    SELECT c.FirstName, c.MiddleName, c.LastName, c.DateOfBirth
                    EXCEPT
                    SELECT u.FirstName, u.MiddleName, u.LastName, u.DateOfBirth
                )
THEN
    UPDATE SET c.FirstName    = u.FirstName,
                c.MiddleName  = u.MiddleName,
                c.LastName    = u.LastName,
                c.DateOfBirth = u.DateOfBirth
WHEN NOT MATCHED BY TARGET
THEN
    INSERT (CustomerID, FirstName, MiddleName, LastName, DateOfBirth)
    VALUES (u.CustomerID, u.FirstName, u.MiddleName, u.LastName, u.DateOfBirth);