One of the new features of SQL Server 2008 is the MERGE statement. Basically MERGE will insert data to a target table from a source if the data is not there other wise it will update/delete from the target table. Using the MERGE statement will actually improves the performance a lot.
1. To further illustrate I have created a table called Mobiles as follows.
CREATE TABLE Mobiles (
ProductID INT PRIMARY KEY,
ManufacturerName NVARCHAR(200),
Price INT
)
2. I inserted the following sample data into the table
INSERT
INTO Mobiles VALUES (1,‘Nokia’,10000),
(2,‘Samsung’,2000),
(3,‘Motorola’,3000)
3. Next I created a table type which will be used as a table valued input parameter for the stored procedures that I create.
CREATE TYPE MobileType AS TABLE
(
MobileID INT PRIMARY KEY,
MobileName NVARCHAR(200),
MobilePrice INT
)
GO
4. Next I created a stored procedure which will insert/update the details of the Mobiles. This procedure accepts the MobileType table valued parameter that I created in the previous step as input.
CREATE
PROCEDURE UpdateMobileDetails
@Mobiles MobileType READONLY
AS
UPDATE M
SET
M.ManufacturerName=LM.MobileName,
M.Price=LM.MobilePrice
FROM Mobiles M
INNER
JOIN @Mobiles LM
ON M.ProductID=LM.MobileID
INSERT
INTO Mobiles
(
ProductID,
ManufacturerName,
Price
)
SELECT
LM.MobileID,
LM.MobileName,
LM.MobilePrice
FROM Mobiles M
LEFT OUTER JOIN @Mobiles LM
ON M.ProductID=LM.MobileID
WHERE M.ProductID IS NULL
This procedure will update the records if they are already existing in the table otherwise it will insert them
5. You can achieve the same functionality using the MERGE statement of the SQL Server 2008.
CREATE
PROCEDURE UpdateMobileDetails_Merge
@Mobiles MobileType READONLY
AS
MERGE Mobiles M
USING @Mobiles LM ON M.ProductID = LM.MobileId
WHEN MATCHED THEN
UPDATE
SET
M.ManufacturerName=LM.MobileName,
M.Price=LM.MobilePrice
WHEN NOT MATCHED THEN
INSERT
(
ProductID,
ManufacturerName,
Price
)
VALUES(
LM.MobileID,
LM.MobileName,
LM.MobilePrice );
If the record matches it updates, if it is not matched it will insert into the table.
6. Next I opened two separate query windows to test these two procedures and analyze the performances. I created the following test scripts in these two windows.
–To test the normal procedure
–Test script
DECLARE @ltblMobiles MobileType
INSERT
INTO @ltblMobiles VALUES (1,‘Nokia N Series’,20000),
(4,‘Sony Ericssion’,5000)
EXEC UpdateMobileDetails
@Mobiles=@ltblMobiles
SELECT
*
FROM Mobiles
–To test the MERGE procedure
DECLARE @ltblMobiles MobileType
INSERT
INTO @ltblMobiles VALUES (6,‘BENQ’,12000),
(7,‘iPhone’,5000)
EXEC UpdateMobileDetails_Merge
@Mobiles=@ltblMobiles
SELECT
*
FROM Mobiles
7. Now go to the Object Explorer. Right Click the database where you have created the table in Step 1.
Right Click database->Reports->Standard Reports ->Object Execution Statistics.
Object Execution statics window will open in a new tab. In that you can see the one which uses MERGE has less Logical I/O.
