?
Solved

What is Merge in SQL server 2010?

Posted on 2014-01-23
4
Medium Priority
?
209 Views
Last Modified: 2014-01-24
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
Comment
Question by:RajG1978
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39805538
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39805543
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
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39806162
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
 

Author Closing Comment

by:RajG1978
ID: 39806980
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question