?
Solved

What is Merge in SQL server 2010?

Posted on 2014-01-23
4
Medium Priority
?
210 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 30

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

650 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