SQL Server Coding Standards: Best practices for MERGE Statement

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

MERGE statement is a new feature introduced in SQL Server 2008 and is very helpful in performing multiple operations like INSERT, UPDATE and DELETE on the same table within a single statement.

Wherever possible, use MERGE statement when a particular operation needs to be carried out based on the condition of existence or non-existence of particular records.

One application of MERGE statement is when you want to insert new records with some Primary Key values if they don’t exist and update the records with the new column values if records already exist with the same Primary Key values.
For example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

MERGE [dbo].[tbl_Customers] AS T USING ( VALUES ( 'CUST005' ,'SomeFirstName5' ,'SomeLastName5' ,'19820101' ,'SomeEmailAddress5@domain.com' ,'F' ) ,( 'CUST006' ,'SomeFirstName6' ,'SomeLastName6' ,'19820101' ,'SomeEmailAddress6@domain.com' ,'F' ) ) AS S (CustomerID,FirstName,LastName,BirthDate,EmailAddress,Gender) ON T.CustomerID = S.CustomerID WHEN NOT MATCHED BY TARGET THEN INSERT ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( S.CustomerID ,S.FirstName ,S.LastName ,S.BirthDate ,S.EmailAddress ,S.Gender ) WHEN MATCHED THEN UPDATE SET CustomerID = S.CustomerID ,FirstName = S.FirstName ,LastName = S.LastName ,BirthDate = S.BirthDate ,EmailAddress = S.EmailAddress ,Gender = S.Gender;

Had this task be done without using MERGE statement, then you would have to write multiple INSERT and UPDATE statements for each record with the check of existence of a particular record by a particular Primary Key using IF EXISTS(). MERGE statement simplifies this task and
make the code more manageable.

Another application of MERGE is when you want to synchronize two tables.

For example, if there are tbl_Orders and tbl_OrderHistory tables and at the end of the week you synchronize these tables in a way so that new orders from tbl_Orders table should be inserted in tbl_OrderHistory table, deleted orders from tbl_Orders table should be deleted from tbl_OrderHistory table and updated orders from tbl_Orders table should be updated in tbl_OrderHistory table then rather than using multiple INSERT, UPDATE, DELETE statements with complex conditions, perform such task with MERGE statement.