SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE

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.

To try them out, you’ll need to first run the following script to create and populate the tables used in the examples:

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);

Implementing the Merge 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;