What is Merge In SQL Server ?

Question

Brief explanation about Merge Join in SQL Server

Answer 1

In SQL Server 2008, you can perform insert, update, or delete operations in a single statement using the MERGE statement. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join.

Answer 2

Starting with SQL Server 2008, you 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.

Answer 3

The MERGE statement supports several clauses that facilitate the different types of data modifications. In this article, I explain each of these clauses and provide examples that demonstrate how they work. I created the examples on a local instance of SQL Server 2008. To try them out, you’ll need to first run the following script to create and populate the tables used in the examples:

USE AdventureWorks2008

IF OBJECT_ID ('BookInventory', 'U') IS NOT NULL
DROP TABLE dbo.BookInventory;

CREATE TABLE dbo.BookInventory -- target
(
TitleID INT NOT NULL PRIMARY KEY,
Title NVARCHAR(100) NOT NULL,
Quantity INT NOT NULL
CONSTRAINT Quantity_Default_1 DEFAULT 0
);

IF OBJECT_ID ('BookOrder', 'U') IS NOT NULL
DROP TABLE dbo.BookOrder;

CREATE TABLE dbo.BookOrder -- source
(
TitleID INT NOT NULL PRIMARY KEY,
Title NVARCHAR(100) NOT NULL,
Quantity INT NOT NULL
CONSTRAINT Quantity_Default_2 DEFAULT 0
);

INSERT BookInventory VALUES
(1, 'The Catcher in the Rye', 6),
(2, 'Pride and Prejudice', 3),
(3, 'The Great Gatsby', 0),
(5, 'Jane Eyre', 0),
(6, 'Catch 22', 0),
(8, 'Slaughterhouse Five', 4);

INSERT BookOrder VALUES
(1, 'The Catcher in the Rye', 3),
(3, 'The Great Gatsby', 0),
(4, 'Gone with the Wind', 4),
(5, 'Jane Eyre', 5),
(7, 'Age of Innocence', 8);

Answer 4

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. Rows are considered matching when the joined column values are the same.

For example, if the BookID value in the BookInventory table matches the BookID value in the BookOrder table, the rows are considered to match, regardless of the other values in the matching rows. When rows do match, you can use the WHEN MATCHED clause to modify data in the target table. Lets look at an example to demonstrate how this works.

In the following MERGE statement, I join the BookInventory table (the target) to the BookOrder table (the source) and then use a WHEN MATCHED clause to update the Quantity column in the target table:

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity;

SELECT * FROM BookInventory;

Answer 5

Home SQL Learn SQL Server The MERGE Statement in SQL Server 2008
27 AUGUST 2010
The MERGE Statement in SQL Server 2008

When the SQL MERGE statement was introduced in SQL Server 2008, it allowed database programmers to replace reams of messy code with something quick, simple and maintainable. The MERGE syntax just takes a bit of explaining, and Rob Sheldon is, as always, on hand to explain with plenty of examples.

534
20

Printer friendly version
Robert Sheldon Robert Sheldon
Starting with SQL Server 2008, you 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.

The MERGE statement supports several clauses that facilitate the different types of data modifications. In this article, I explain each of these clauses and provide examples that demonstrate how they work. I created the examples on a local instance of SQL Server 2008. To try them out, you’ll need to first run the following script to create and populate the tables used in the examples:

USE AdventureWorks2008

IF OBJECT_ID ('BookInventory', 'U') IS NOT NULL
DROP TABLE dbo.BookInventory;

CREATE TABLE dbo.BookInventory -- target
(
TitleID INT NOT NULL PRIMARY KEY,
Title NVARCHAR(100) NOT NULL,
Quantity INT NOT NULL
CONSTRAINT Quantity_Default_1 DEFAULT 0
);

IF OBJECT_ID ('BookOrder', 'U') IS NOT NULL
DROP TABLE dbo.BookOrder;

CREATE TABLE dbo.BookOrder -- source
(
TitleID INT NOT NULL PRIMARY KEY,
Title NVARCHAR(100) NOT NULL,
Quantity INT NOT NULL
CONSTRAINT Quantity_Default_2 DEFAULT 0
);

INSERT BookInventory VALUES
(1, 'The Catcher in the Rye', 6),
(2, 'Pride and Prejudice', 3),
(3, 'The Great Gatsby', 0),
(5, 'Jane Eyre', 0),
(6, 'Catch 22', 0),
(8, 'Slaughter

Answer 6

Implementing the WHEN NOT MATCHED [BY TARGET] Clause

The next clause in the MERGE statement we’ll review is WHEN NOT MATCHED [BY TARGET]. (The BY TARGET keywords are optional.) You should use this clause to insert new rows into the target table. The rows you insert into the table are those rows in the source table for which there are no matching rows in the target. For example, the BookOrder table contains a row for Gone with the Wind. However, the BookInventory table does not contain this book. The following example demonstrates how to include a WHEN NOT MATCHED clause in your MERGE statement that adds Gone with the Wind to your target table:

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED AND
bi.Quantity + bo.Quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET THEN
INSERT (TitleID, Title, Quantity)
VALUES (bo.TitleID, bo.Title,bo.Quantity);

SELECT * FROM BookInventory;

Answer 7

Implementing the WHEN NOT MATCHED BY SOURCE Clause

As you’ll recall from the discussion about the WHEN MATCHED clause, you can use that clause to delete rows from the target table. However, you can delete a row that matches a row in the source table. But suppose you want to delete a row from the target table that does not match a row in the source table.

For example, one of the rows originally inserted into the BookInventory table is for the book Catch 22. The Quantity value for that book was never updated because no order was placed for the book, that is, the book was never added to the BookOrder table. Because there are no copies of that book in stock, you might decide to delete that book from the target table. To delete a row that does not match a row in the source table, you must use the WHEN NOT MATCHED BY SOURCE clause.
MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED AND
bi.Quantity + bo.Quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET THEN
INSERT (TitleID, Title, Quantity)
VALUES (bo.TitleID, bo.Title,bo.Quantity)
WHEN NOT MATCHED BY SOURCE
AND bi.Quantity = 0 THEN
DELETE;

SELECT * FROM BookInventory;

Answer 8

Implementing the OUTPUT Clause

When SQL Server 2005 was released, it included support for the OUTPUT clause in several data modification language (DML) statements. The OUTPUT clause is also available in the MERGE statement. The OUTPUT clause returns a copy of the data that you’ve inserted into or deleted from your tables. When used with a MERGE statement, the clause provides you with a powerful tool for capturing the modified data for archiving, messaging, or application purposes.
DECLARE @MergeOutput TABLE
(
ActionType NVARCHAR(10),
DelTitleID INT,
InsTitleID INT,
DelTitle NVARCHAR(50),
InsTitle NVARCHAR(50),
DelQuantity INT,
InsQuantity INT
);

MERGE BookInventory bi
USING BookOrder bo
ON bi.TitleID = bo.TitleID
WHEN MATCHED AND
bi.Quantity + bo.Quantity = 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET bi.Quantity = bi.Quantity + bo.Quantity
WHEN NOT MATCHED BY TARGET THEN
INSERT (TitleID, Title, Quantity)
VALUES (bo.TitleID, bo.Title,bo.Quantity)
WHEN NOT MATCHED BY SOURCE
AND bi.Quantity = 0 THEN
DELETE
OUTPUT
$action,
DELETED.TitleID,
INSERTED.TitleID,
DELETED.Title,
INSERTED.Title,
DELETED.Quantity,
INSERTED.Quantity
INTO @MergeOutput;

SELECT * FROM BookInventory;

SELECT * FROM @MergeOutput;