Merge Statement - SQL Server 2008

Another enhancement in the Business Intellegence part is the introduction of Merge statement in SQL Server. Many a times we have faced challenges in keeping the Transactional database and the datawarehouse(Historical data) objects in sync. The update on the datawarhouse (DW) has to happen with the changes that occur on the transactional database on day to day bases. In practise, the DW database is not updated very frequently. Before a DW is updated it has to compared with the source (transactional db) and all the changes in the source will have to reflect in the DW database. The question is, Why to write separate (insert, update, delete) DML statement for keeping tables in sync?



Now we introduce the concept of merging the 3 DML statements into a single T-SQL. That's the Merge Statement.



Following is the syntax for the Merge Statement.




MERGE
USING
ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN [TARGET] NOT MATCHED [ AND ]
THEN ]
[ WHEN SOURCE NOT MATCHED [ AND ]
THEN ]



So, let us see the various senarios using an example.




IF OBJECT_ID('tblCustomer','U') IS NOT NULL
DROP TABLE dbo.tblCustomer
GO
CREATE TABLE dbo.tblCustomer
(CustomerID int NOT NULL, CustomerName varchar(20) NOT NULL,City varchar(20) NOT NULL)
GO


IF OBJECT_ID('tmpCustomer','U') IS NOT NULL
DROP TABLE dbo.tmpCustomer
GO
CREATE TABLE dbo.tmpCustomer
(CustomerID int NOT NULL, CustomerName varchar(20) NOT NULL,City varchar(20) NOT NULL)


GO
INSERT INTO tblCustomer(CustomerID,CustomerName,City)
VALUES (1,'James','Chennai'),
(2,'John','Chennai'),
(3,'Debbie','Bangalore')


GO
INSERT INTO tmpCustomer(CustomerID,CustomerName,City)
VALUES (1,'James','Chennai'),
(3,'Debbie','Delhi'),
(4,'Nancy','Bangalore'),
(5,'Mala','Chennai')


GO
SELECT * FROM tblCustomer
SELECT * FROM tmpCustomer



In this example tblCustomer is the target table which will updated based source tmpCustomer and the update on the target table will happen based on the CustomerID column.



Scenario 1:


If the CustomerID matches with the target table then the city has to updated on the target table

Scenario 2:

If the CustomerID is in the source but not in the target table, then the record has to be inserted into target table. This happens when a new prouct gets introduced into the market and the target table is unware of the change.

Scenario 3:

If the CustomerID in the target table does not match records in the source table, then the record in the target table can be deleted. Most of times the data in DW does not get deleted for such reasons.

MERGE tblCustomer as Main
USING tmpCustomer tmp
ON Main.CustomerID = tmp.CustomerID
WHEN MATCHED THEN -- Scenario 1
UPDATE SET City = tmp.City
WHEN NOT MATCHED BY SOURCE THEN -- Scenario 3
DELETE
WHEN NOT MATCHED BY TARGET THEN -- Scenario 2
INSERT (CustomerID,CustomerName,City)
VALUES (tmp.CustomerID,tmp.CustomerName,tmp.City);

Thats it!! We are done. On top it you also have an option to investigate your target table to check for the changes. You need to use an action keyword with your Merge statement. Delete the values of both source and target table and then insert the values again. Then run the following query,

MERGE tblCustomer as Main
USING tmpCustomer tmp
ON Main.CustomerID = tmp.CustomerID
WHEN MATCHED THEN
UPDATE SET City = tmp.City
WHEN NOT MATCHED BY SOURCE THEN
DELETE
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID,CustomerName,City)
VALUES (tmp.CustomerID,tmp.CustomerName,tmp.City)
OUTPUT $action,tmp.CustomerID;

Do write in your queries and comments. Will catch up with some other features soon.