Dot Net Rules

Looking into the MERGE T-SQL 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)

[sourcecode language="sql"]
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;

[/sourcecode]

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!!!