Merge Statement - SQL Server 2008

I am going to tell you about the new feature in Sql server 2008 "MERGE"statement.

It is used to perform insert , update, delete statement in a single query on a target table by joining to a source table

Earlier versions of MSSQL , when Merge statement was not present we had to run multiple operation of update/insert/delete 
on a table to synchronize it with other table
With Merge we can do it in a single query.

Lets try to understand it with an Example

Here is the syntax for Merge Statement from Books Online


MERGE
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias ]
        USING
        ON
        [ WHEN MATCHED [ AND ]
            THEN ]
        [ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
            THEN ]
        [ WHEN NOT MATCHED BY SOURCE [ AND ]
            THEN ]
        [ ]
        [ OPTION ( [ ,...n ] ) ]   
;

MERGE clause specify target table on which insert/update/delete operation runs
USING clause specify the source used to join with target table
WHEN MATCHED is equal to inner join . Where target table rows are updtaed/ deleted which are matched to source table
depeneding on merge search condition .
We can have atmost two WHEN MATCHED  caluse , with first caluse with an additional searchcondition


Lets see example of WHEN MATCHED

we have 2 tables

1) sales  (Source table)
2) inventory (target table)

when the sales happens then, the quantity of the sales gets reduced from inventory balance.

CREATE TABLE Inventory
(
     product_id     INT IDENTITY(1,1)
    ,product_name   VARCHAR(50)
    ,quantity        INT
    ,description    VARCHAR(50)   
    ,creation_date  DATETIME
    ,updation_date  DATETIME        

)

INSERT INTO Inventory (product_name,quantity,description,creation_date,updation_date)
SELECT 'T-Shirt',10,'Polo T-Shirt',getdate(),getdate()
UNION
SELECT 'Shirt',10,'Parks Casual Shirt',getdate(),getdate()
UNION
SELECT 'Jeans',10,'Levis',getdate(),getdate()
UNION
SELECT 'Belt',10,'Dockers',getdate(),getdate()



CREATE TABLE Sales
(
     sales_id      INT IDENTITY
    ,product_id    INT
    ,quantity      INT
    ,creation_date DATETIME
    ,updation_date DATETIME
)   

Insert Sales (product_id,quantity,creation_date,updation_date)
SELECT 1,5,getdate(),getdate()
UNION
SELECT 2,10,getdate(),getdate()
UNION
SELECT 3,5,getdate(),getdate()
UNION
SELECT 4,5,getdate(),getdate()

Now we will have to synchronize  the inventory  with sales table
Since product 2 (shirts) all are sold then we will have to delete its rows from inventory and
for all other we will subtract sales quantity from inventory quantity


syntax using example:-


select * from Inventory
select * from Sales


MERGE Inventory as I
USING Sales as S on I.product_id = S.product_id
WHEN MATCHED and I.quantity - S.quantity <= 0
    THEN
        DELETE
WHEN MATCHED and I.quantity - S.quantity > 0
    THEN
    UPDATE
    SET  I.quantity = I.quantity - S.quantity
        ,I.updation_date = getdate()

OUTPUT
$action
, deleted.product_id , deleted.quantity
, inserted.product_id , inserted.quantity ;


select * from Inventory
select * from Sales   


Notes:-
A MERGE statement must be terminated by a semi-colon (;).
$action will give type of operation (insert/update/delete)