[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

What is Merge in SQL server 2010?

I have used SQL Server2008 previously but haven't got a chance to use SQL Server 2010.
I was been told that in SQL Server 2010 there has been a new feature been added.
When I want to do insert,delete and update for a transaction I can use "Merge" in a single statement instead of using multiple statements. Is it true. If so, can anyone please explain how I can use this feature?
0
RajG1978
Asked:
RajG1978
1 Solution
 
chaauCommented:
It is all true.
However, let's start from the beginning. There is no such a version SQL Server 2010.
There is 2008, 2008 R2, and 2012. After 2012 there will be 2014 (currently in preview stage).
As for the MERGE statement, it is not new. It exists since version 2008.
The statement is a powerful tool allowing to do INSERT, DELETE, UPDATE in a single statement based on data integrity rules you define in its conditions.
I recommend you first read the MSDN article and try to understand if everything is clear.
One small example:
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED 
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
                    target.ModifiedDate = GETDATE()

Open in new window

to translate the above statement to English:
provided you have a "target" table "Production.ProductInventory" you need to insert new entries produced from the "source" represented as a query: "SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod JOIN Sales.SalesOrderHeader AS soh ON sod.SalesOrderID = soh.SalesOrderID GROUP BY ProductID", for the products that not exist in the target "target.ProductID = source.ProductID". When there are already such products then do the following:
If "target.Quantity - source.OrderQty <= 0" then DELETE such record from target,
for other matching records, just update target's values from the source, using the following statement: "SET target.Quantity = target.Quantity - source.OrderQty,                     target.ModifiedDate = GETDATE()"
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SQL 2008 --> 2008R2 --> 2012, there is no 2010.

The T-SQL Merge statement, introduced in 2008, is a way to perform an insert, update, and delete all in one statement, so yes it's true.   If you scroll down in the link you'll see how it's used.

I've used it a bunch of times in data warehouse situations, where clients send you a full-boat file, and the developer needs to determine which of the file are new rows (i.e. insert), changed rows (i.e. update), and if rows are in the target but not in the file, delete.   This greatly decreases the speed of the entire operation.

The pre-MERGE way of doing this involved multiple statements with a wompload of JOINs, and processing the entire set multiple times.
0
 
Olaf DoschkeSoftware DeveloperCommented:
To Jim Horn: Decreases the speed? You meant to say decreases the processing time or increase the speed.

To RajG1978: Indeed you have to delve a bit into the syntax and it's options. Watch out for the match condition, it can be specified in different ways for source and target. MERGE is interesting with staging databases or tables or text files, not so much with .NET Entities or Data tables committing their changes. At least if you ask me, it could and should get part of the automatisms behind the scenes and not our job to override standard INSERT/UPDATE/DELETE operations with a MERGE in ORM.

So where do you intend to use MERGE?

Bye, Olaf.
0
 
RajG1978Author Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now