Marge statement at SQL 2005

Introduction

As all we now that the MARGE statement is a special feature of MS SQL 2008 only.  But by programmatically we can create it in MS SQL Server 2005.

In this article I am trying to manage the MARGE statement at MS SQL 2005. Here I am not describing the MARGE statement. I just use it as an Example.

IF OBJECT_ID (N'dbo.Tbl_BookInventory', N'U') IS NOT NULL

   BEGIN

          DROP TABLE dbo.Tbl_BookInventory;

   END

GO  

-- Creating the Target Table

CREATE TABLE dbo.Tbl_BookInventory

(

  TitleID     INT             NOT NULL PRIMARY KEY,

  Title            NVARCHAR(100) NOT NULL,

  Quantity         INT             NOT NULL

  CONSTRAINT Quantity_Default_1 DEFAULT 0

);

GO

IF OBJECT_ID (N'dbo.Tbl_BookOrder', N'U') IS NOT NULL

   BEGIN

          DROP TABLE dbo.Tbl_BookOrder;

   END       

GO

-- Creating the Source Table

CREATE TABLE dbo.Tbl_BookOrder

(

  TitleID     INT           NOT NULL PRIMARY KEY,

  Title       nVARCHAR(100) NOT NULL,

  Quantity    INT           NOT NULL

  CONSTRAINT Quantity_Default_2 DEFAULT 0

);

GO

--Inserting Records In Target Table

INSERT INTO dbo.Tbl_BookInventory

       (TitleID, Title, Quantity)    

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

GO 

-- Inserting Record in Source Table 

INSERT INTO dbo.Tbl_BookOrder

       (TitleID, Title, Quantity)    

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

GO 

-- The Marge statement

MERGE dbo.Tbl_BookInventory bi

USING dbo.Tbl_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);

GO 

----------------------------------

-- Marge Statement in SQL 2005 --

----------------------------------

BEGIN

  DECLARE @tblContacts table (ContId INT);

  -- Update Statement     

  UPDATE bi

     SET bi.Quantity = bi.Quantity + bo.Quantity

     OUTPUT inserted.TitleID INTO  @tblContacts

  FROM Tbl_BookInventory AS bi

       INNER JOIN Tbl_BookOrder AS bo

                 ON bi.TitleID = bo.TitleID;

  -- Delete Statement

  DELETE bi

  FROM   Tbl_BookInventory AS bi

         INNER JOIN Tbl_BookOrder AS bo

         ON bi.TitleID = bo.TitleID

            AND (bi.Quantity+bo.Quantity)=0;

  -- Insert Statement

  INSERT INTO Tbl_BookInventory

       (TitleID, Title, Quantity)

  SELECT TitleID, Title, Quantity

  FROM   dbo.Tbl_BookOrder

  WHERE  TitleID NOT IN (SELECT ContId

                         FROM   @tblContacts);

END

SELECT * FROM Tbl_BookInventory

TitleID       Title                   Quantity

1             The Catcher in the Rye  9

2             Pride and Prejudice     3

4             Gone with the Wind      4

5             Jane Eyre               5

6             Catch 22                0

7             Age of Innocence        8

8             Slaughterhouse Five     4

Hope you like it.

Posted by: MR. JOYDEEP DAS