SQL Server 2008 – Introduction to Merge Statement, One Statement for INSERT, UPDATE, DELETE.

In previous versions of SQL Server, when you heard the word ‘‘merge’’ you generally thought of merge replication. With SQL Server 2008, however, we have a whole new way of thinking about the word merge and, more importantly, of thinking about DML statements.

One of the most important advantages of MERGE statement is all the data is read and processed only once. This is quite an improvement in performance of database query.

With MERGE, we have the prospect of combining multiple DML action statements (INSERT, UPDATE, and DELETE) into one overall action, improving performance (they can share many of the same physical operations) and simplifying transactions. MERGE makes use of a special USING clause that winds up working somewhat like a CTE. The result set in the USING clause can then be used to conditionally apply your INSERT, UPDATE, and DELETE statements. The basic syntax looks something like this:

Syntax of MERGE statement is as following:
MERGE <target table> [AS <alias>]

USING

(

<source query>

)

[ON <relation-condition-mergANDqueryTable>]

WHEN {[NOT] MATCHED | <expression> THEN

<action statement>

[<additional WHEN clauses>, [...n]]

N.B.: no Need to give table name for insert, update and delete, because its take automatically MERGE table Name.

Example:

Create Marge Table……………………….

CREATE TABLE Sales.MonthlyRollup(

Year smallint NOT NULL,

Month tinyint NOT NULL,

ProductID int NOT NULL

FOREIGN KEY REFERENCES Production.Product(ProductID),

QtySold int NOT NULL,

CONSTRAINT PKYearMonthProductID

PRIMARY KEY (Year, Month, ProductID)

);

Let’s you keep sales data into MERGE table, every day after sales complete, you need to update sales of every product, if your product is first time sales today then you need to insert this product sales, if sales quantity 0 then need delete, then the quey is following:

MERGE Sales.MonthlyRollup AS smr

USING

(

SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold

FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod

ON soh.SalesOrderID = sod.SalesOrderID

WHERE soh.OrderDate =’23 May 2012’

GROUP BY soh.OrderDate, sod.ProductID

)AS s

ON (s.ProductID = smr.ProductID)

WHEN MATCHED AND smr.QtySold =0 AND  s.QtySold =0 THEN DELETE

WHEN MATCHED THEN UPDATE SET smr.QtySold = smr.QtySold + s.QtySold

WHEN NOT MATCHED THEN INSERT (Year, Month, ProductID, QtySold)

VALUES (DATEPART(yy, s.OrderDate),DATEPART(m, s.OrderDate),s.ProductID,s.QtySold);