SQL Server 2008 - MERGE Function

I am quite impressed with the MERGE function.. in SQL Server 2008, which is a new feature added.

lets take a simplest example to understand MERGE Function.

let's say we got a PurchaseRecords table, in which we are storing customerId and productId and PurchaseDate...

here is code to create a table with all indexes..

IF OBJECT_ID (N'dbo.PurchaseRecords', N'U') IS NOT NULL
DROP TABLE dbo.PurchaseRecords;
GO

CREATE TABLE PurchaseRecords (ProductID int, CustomerID int, PurchaseDate datetime);
GO

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_PurchaseRecords_ProductID')
DROP INDEX IX_PurchaseRecords_ProductID ON dbo.PurchaseRecords;
GO

CREATE CLUSTERED INDEX IX_PurchaseRecords_ProductID
ON dbo.PurchaseRecords (ProductID);
GO

and here is the code to insert some dummy records in table.

INSERT INTO PurchaseRecords VALUES(707, 11794, '20060821'),
(707, 15160, '20060825'),(708, 18529, '20060821'),
(711, 11794, '20060821'),(711, 19585, '20060822'),
(712, 14680, '20060825'),(712, 21524, '20060825'),
(712, 19072, '20060821'),(870, 15160, '20060823'),
(870, 11927, '20060824'),(870, 18749, '20060825');
GO


now we gona have a table which is going to store information regards to CustomerPurchaseHabit..

and code for that is here..

IF OBJECT_ID (N'dbo.FactBuyingHabits', N'U') IS NOT NULL
DROP TABLE dbo.FactBuyingHabits;
GO

CREATE TABLE FactBuyingHabits (ProductID int, CustomerID int, LastPurchaseDate datetime);
GO

IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_FactBuyingHabits_ProductID')
DROP INDEX IX_FactBuyingHabits_ProductID ON dbo.FactBuyingHabits;
GO

CREATE CLUSTERED INDEX IX_FactBuyingHabits_ProductID
ON dbo.FactBuyingHabits (ProductID);
GO


INSERT INTO FactBuyingHabits VALUES(707, 11794, '20060814'),
(707, 18178, '20060818'),(864, 14114, '20060818'),
(866, 13350, '20060818'),(866, 20201, '20060815'),
(867, 20201, '20060814'),(869, 19893, '20060815'),
(870, 17151, '20060818'),(870, 15160, '20060817'),
(871, 21717, '20060817'),(871, 21163, '20060815'),
(871, 13350, '20060815'),(873, 23381, '20060815');
GO


ok now what we want to do is.. we want to go through each records in PurchaseRecords and check whether this information exists in FactBuyingHabits or not.. if it exists then update its date if it doesnt then insert it, now if we want to do this.. we will go about creating cursor then if condition inside it and do it.. what do we get? we get result.. but cursors are very slow... in SQL Server 2008 Microsoft introduced MERGE function which allows us to do this pretty easily, and performance is increased pretty good.

and code for this is something like following:

MERGE FactBuyingHabits AS fbh
USING (SELECT CustomerID, ProductID, PurchaseDate FROM PurchaseRecords) AS src
ON (fbh.ProductID = src.ProductID AND fbh.CustomerID = src.CustomerID)
WHEN MATCHED THEN
UPDATE SET fbh.LastPurchaseDate = src.PurchaseDate
WHEN NOT MATCHED THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (src.CustomerID, src.ProductID, src.PurchaseDate);