Solved

What is Merge in SQL server 2010?

Posted on 2014-01-23
4
204 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 500 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 65

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

756 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