Solved

What is Merge in SQL server 2010?

Posted on 2014-01-23
4
200 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
4 Comments
 
LVL 24

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error in WHERE Clause 5 41
SQL Server - Slabs 9 38
Splitting the content of a column in SQL 11 21
Need a SQL Server 2014 plug-in to scan the DB schema 4 16
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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now