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 NOTNULL
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.
