RSS Feed

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