I used to review new stored procedure which is going to be live. Recently I have seen that one of the developers has developed one stored procedure which had logic of inserting records if it doesn’t exists already and update some value if record is already exists. He has used complex logic with IF…ELSE and SELECT…EXISTS. Since we are having SQL Server 2008 R2 in that particular server, it is not need to develop complex logic but we can simply use MERGE statement which was introduced from SQL Server 2008.
MERGE statement was one of the very good enhancements in TSQL in SQL Server 2008 though people don’t use to use it much due to unawareness.
MERGE statement will allow you to use any combination of INSERT, UPDATE & DELETE with simple MERGE statement and without having any complex logic which used to have till SQL Server 2005. Apart from that MERGE statement read and process data at once so that you reduce roundtrip of the server which ultimately enhances the performance.
USE AdventureWorks2012 GO MERGE sales.SalesOrderDetail AS SOD USING (SELECT SalesOrderID,CustomerID,DueDate FROM Sales.SalesOrderHeader) AS SOH ON SOD.SalesOrderID=SOH.SalesOrderID WHEN MATCHED AND SOH.SalesOrderID=43659 AND SOD.ProductID=776 THEN DELETE WHEN MATCHED AND SOH.SalesOrderID=43659 AND SOD.ProductID=777 THEN UPDATE SET SOD.OrderQty=11 WHEN NOT MATCHED THEN INSERT(SalesOrderID,CarrierTrackingNumber,OrderQty,ProductID) VALUES(SOH.SalesOrderID,'4911-403C-98',11,776); GO
Above query will delete one record from SalesOrderDetail table and Update one record in SalesOrderDetail table in AdventureWorks database. It won’t insert any record as there is no record which is exists in SaleOrderHeader table which doesn’t have child entry in SalesOrderDetails table.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.