How to use Merge Statement is Sql Server ?

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.

-- -- Update existing, add missing

MERGE INTO dbo.tbl_Customers AS C

USING dbo.tbl_CustomersTemp AS CT

        ON C.CustID = CT.CustID

WHEN MATCHED THEN

    UPDATE SET

      C.CompanyName = CT.CompanyName,

      C.Phone = CT.Phone

WHEN NOT MATCHED THEN

      INSERT (CustID, CompanyName, Phone)

      VALUES (CT.CustID, CT.CompanyName, CT.Phone)

CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);

CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);

--Synchronize source data with target

MERGE INTO dbo.tbl_Target AS t

    USING dbo.tbl_Source AS s   

        ON t.id = s.id

    WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN

        --Row exists and data is different

        UPDATE SET t.name = s.name, t.qty = s.qty

    WHEN NOT MATCHED THEN

        --Row exists in source but not in target

        INSERT VALUES (s.id, s.name, s.qty)

    WHEN SOURCE NOT MATCHED THEN

        --Row exists in target but not in source

        DELETE

In this above post I explained hoiw to use  Merge Statement in Sql server. I hpe you enjoyed it so please send your feedback and queries. Thnak You.