Example of Merge Statement

Example of Merge Statement

Let us take a simple example of merge statement:

There are two tables Mobiles and Mobiles_New. We have to update the Mobiles table based on the Mobiles_New table so that:

1. Mobiles that exist in both the Mobiles_New table and the Mobiles table are updated in the Mobiles table with new names.
2. Any Mobiles in the Mobiles table that do not exist in the Mobiles_New table must be deleted from the Mobiles table.
3. Any Mobiles in the Mobiles_New table that do not exist in the Mobiles table are inserted into the Mobiles table.

Lets create the tables and insert values into them:

CREATE TABLE [Mobiles]
    (
      [MobileID] [int] PRIMARY KEY,
      [MobileName] [nvarchar](65),
      [Company] [nvarchar](55)
    );

CREATE TABLE [Mobiles_New]
    (
      [MobileID] [int] PRIMARY KEY,
      [MobileName] [nvarchar](65),
      [Company] [nvarchar](55)
    );

      INSERT  INTO [Mobiles]
VALUES  ( 1, 'Nokia 100', 'MS' )
,       ( 2, 'Nokia 120', 'MS' )
,       ( 3, 'Nokia 130', 'MS' )
,       ( 4, 'N72', 'Nokia' )
,       ( 5, 'P21', 'Panaonic' )
,       ( 6, 'M69', 'Micromax' );

INSERT  INTO [Mobiles_New]
VALUES  ( 1, 'Microsoft 100', 'Microsoft' )
,       ( 2, 'Microsoft 120', 'Microsoft' )
,       ( 3, 'Microsoft 130', 'Microsoft' )
,       ( 4, 'Nokia 72', 'Microsoft' )
,       ( 7, 'iPhone', 'Apple' );

Select * from Mobiles

Result:

MobileID	MobileName	Company
1	Nokia 100	MS
2	Nokia 120	MS
3	Nokia 130	MS
4	N72	Nokia
5	P21	Panasonic
6	M69	Micromax

Select * from Mobiles_New

Result:

MobileID	MobileName	Company
1	Microsoft 100	Microsoft
2	Microsoft 120	Microsoft
3	Microsoft 130	Microsoft
4	Nokia 72	Microsoft
7	iPhone	Apple

Now lets merge the above two tables based on the points given at the beginning of the post:

MERGE [dbo].[Mobiles] AS [OLD_Tbl]
USING [dbo].[Mobiles_New] AS [New_Tbl]
ON ( OLD_Tbl.[MobileID] = New_Tbl.[MobileID] )
WHEN MATCHED AND OLD_Tbl.[MobileName] <> 
New_Tbl.[MobileName]
    OR OLD_Tbl.[Company] <> New_Tbl.[Company] THEN
    UPDATE SET OLD_Tbl.[MobileName] = New_Tbl.[MobileName] 
,
               OLD_Tbl.[Company] = New_Tbl.[Company]
WHEN NOT MATCHED THEN
    INSERT ( [MobileID] ,
             [MobileName] ,
             [Company]
           )
    VALUES ( New_Tbl.[MobileID] ,
             New_Tbl.[MobileName] ,
             New_Tbl.[Company]
           )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $Action ,
    INSERTED.*,
    DELETED.*;
GO

Result:

$Action	MobileID	MobileName	Company	MobileID	MobileName	Company
UPDATE	1	Microsoft 100	Microsoft	1	Nokia 100	MS
UPDATE	2	Microsoft 120	Microsoft	2	Nokia 120	MS
UPDATE	3	Microsoft 130	Microsoft	3	Nokia 130	MS
UPDATE	4	Nokia 72	Microsoft	4	N72	Nokia
DELETE	NULL	NULL	NULL	5	P21	Panasonic
DELETE	NULL	NULL	NULL	6	M69	Micromax
INSERT	7	iPhone	Apple	NULL	NULL	NULL

Also see:
SQL Merge