How to implement MERGE statement in SQL Server

SQL Server provides the MERGE statement that allows you to perform three actions (INSERT/UPDATE/DELETE) at the same time. 

The following shows the syntax of the MERGE statement:

MERGE target_table USING source_table ON merge_condition WHEN MATCHED THEN update_statement WHEN NOT MATCHED THEN insert_statement WHEN NOT MATCHED BY SOURCE THEN DELETE;

To implement MERGE Statement, if you run the below example, you can easily learn this concept.

DECLARE  @category AS TABLE (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);

INSERT INTO @category(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (2,'Comfort Bicycles',25000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',10000);


DECLARE  @category_staging AS TABLE(
    category_id INT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);


INSERT INTO @category_staging(category_id, category_name, amount)
VALUES(1,'Children Bicycles',15000),
    (3,'Cruisers Bicycles',13000),
    (4,'Cyclocross Bicycles',20000),
    (5,'Electric Bikes',10000),
    (6,'Mountain Bikes',10000);

SELECT * FROM @category
SELECT * FROM @category_staging


MERGE @category t 
USING @category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
;

SELECT * FROM @category