Looking into the MERGE T-SQL statement November 16, 2013Posted by fofo in Sql Server, SQL Server 2008, SQL Server 2012.
Tags: MERGE statement
I have been delivering a certified course in MS SQL Server 2012 recently and I was asked to provide a demo about the MERGE statement.
I will provide you with a demo in this post trying to explain more about the MERGE T-SQL statement and its use.
This statement was introduced back to SQL Server 2008.We can use a MERGE statement to modify data in a target table based on data in a source table.The statement joins the target to the source by using a column common to both tables, such as a primary key.
You can then insert, modify, or delete data from the target table—all in one statement—according to how the rows match up as a result of the join.
You need to have SELECT permissions on the source and INSERT,UPDATE,DELETE permissions on the target.
In this example I will create two tables, one source and one target table. I will use the WHEN MATCHED THEN clause to update rows in the target table that match rows in the source table.
I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012/2008 Express edition as well.
I am connecting to my local instance through Windows Authentication and in a new query window I type (you can copy paste)
USE master GO CREATE DATABASE mergedata GO USE mergedata GO IF OBJECT_ID ('CarsStock', 'U') IS NOT NULL DROP TABLE dbo.CarsStock; -- this is our target table CREATE TABLE dbo.CarsStock ( CarID INT NOT NULL PRIMARY KEY, CarModel NVARCHAR(100) NOT NULL, Quantity INT NOT NULL CONSTRAINT Qt_Df_1 DEFAULT 0 ); IF OBJECT_ID ('CarsOrders', 'U') IS NOT NULL DROP TABLE dbo.CarsOrders; --this is the souce table. CREATE TABLE dbo.CarsOrders ( CarID INT NOT NULL PRIMARY KEY, CarModel NVARCHAR(100) NOT NULL, Quantity INT NOT NULL CONSTRAINT Qt_Df_2 DEFAULT 0 ); INSERT CarsStock VALUES (1, 'BMW Cabrio', 12), (2, 'Ford Focus', 13), (3, 'LexusLS460', 2), (5, 'KIA Preggio', 1), (6, 'Citroen Picasso', 1), (8, 'Ford escape', 4); INSERT CarsOrders VALUES (1, 'BMW Cabrio', 4), (3, 'LexusLS460', 1), (4, 'Citroen Picasso', 4), (5, 'KIA Preggio', 5), (7, 'KIA optima', 8); --Implementing the WHEN MATCHED Clause --The first MERGE clause we’ll look at is -- WHEN MATCHED. You should use this clause -- when you want to update or delete rows -- in the target table that match rows in the -- source table MERGE CarsStock cs USING CarsOrders co ON cs.CarID = co.CarID WHEN MATCHED THEN UPDATE SET cs.Quantity = cs.Quantity + co.Quantity; SELECT * FROM CarsStock;
Let me explain what I am doing in this bit of code
- I make the master database the current database
- I create a new database mergedata
- I create a table called CarsStock
- I create a table called CarsOrders
- I insert values in the CarsStock table – target table
- I insert values in the CarsOrders table -source table
- Then I use the WHEN MATCHED THEN clause with the MERGE statement because I want to update rows(quantity column) in the target table that match rows in the source table
- Then I select the contents of the CarsStock table and see the values updated (Quantity column) where the values of the CarID is the same in the two tables- joined column values are the same.
I will let you investigate the
- WHEN NOT MATCHED [BY TARGET] clause
- WHEN NOT MATCHED BY SOURCE clause
Hope it helps!!!