Using the MERGE statement

In SQL Server, you can perform multiple DML operations in a single code block using the MERGE statement. The MERGE statement is a powerful Transact-SQL language feature that allows you to join a source table with a target table, and then perform multiple DML operations against the specified target table, based on the results of the MERGE statement join conditions. By using a MERGE statement, you can improve the performance of OLTP applications, since the data is processed only once.

To execute a MERGE statement, a user must at least have a SELECT permission assigned on the source table and INSERT, UPDATE, and DELETE permissions assigned on the target table.

The basic syntax for the MERGE statement is as follows:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ]
    USING <source_table>
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ] ;

The following are the arguments of the MERGE statement:

  • WITH: This keyword specifies the CTE that you can reference in your MERGE statement.
  • TOP: You use this keyword only to perform DML operations on a specific number or percent of rows from the top of the joined rows.
  • target_table: This specifies the name of the target table against which the source table rows are matched.
  • USING <source_table>: This specifies the name of the source table that is matched with the target table.
  • ON: This keyword specifies the MERGE statement join conditions to identify which records should be affected.
  • WHEN MATCHED: These keywords specify all rows that exist in both the source and target tables. Based on these matching rows, update or delete data in the target table.
  • WHEN NOT MATCHED [BY TARGET]: These keywords specify all rows in the source table that do not exist in the target table. Based on the results of the <merge_search_condition> condition, you insert the data into the target table. You can only have one WHEN NOT MATCHED clause in the MERGE statement.
  • WHEN NOT MATCHED BY SOURCE: These keywords specifies all rows in the target table that do not exist in the source table. Based on the results of the <merge_search_condition> condition, you either update or delete data in the target table.
  • output_clause: You use this to identify the rows affected by the MERGE statement.
  • OPTION (<query_hint> [ ,...n ]): You use this keyword to specify one or more optimizer query hints.

The MERGE statement examples

To illustrate the MERGE statement, consider the following two tables:

  • dbo.Spices: This database contains information about the spices that the company is currently selling
  • dbo.SpicesNew: This database contains information about the spices that the company will sell in the future

The following T-SQL code creates and populates these two tables:

USE [AdventureWorks2012];
GO

IF OBJECT_ID(N'dbo.Spices', N'U') IS NOT NULL
    DROP TABLE [dbo].[Spices];
GO
CREATE TABLE [dbo].[Spices]
    (
      [SpiceID] [int] PRIMARY KEY,
      [SpiceMixName] [nvarchar](64),
      [Supplier] [nvarchar](50)
    );

IF OBJECT_ID(N'dbo.Spices_New', N'U') IS NOT NULL
    DROP TABLE [dbo].[Spices_New];
GO
CREATE TABLE [dbo].[Spices_New]
    (
      [SpiceID] [int] PRIMARY KEY,
      [SpiceMixName] [nvarchar](64),
      [Supplier] [nvarchar](50)
    );

INSERT  INTO [dbo].[Spices]
VALUES  ( 1, N'Five-spice powder', N'Go it!' )
,       ( 2, N'Curry powder', N'East-end Spices' )
,       ( 3, N'Garam masala', N'All Spices' )
,       ( 4, N'Harissa', N'More Places For Stuff' )
,       ( 5, N'Shichimi togarashI', N'World-wide Supply' )
,       ( 6, N'Mixed spice', N'UK Spices' )
,       ( 7, N'Old Bay Seasoning', N'US Mixed Spices' )
,       ( 8, N'Jerk spice', N'Quality Spices' );

INSERT  INTO [dbo].[Spices_New]
VALUES  ( 1, N'Advieh', N'Outlander Spices' )
,       ( 2, N'Baharat', N'Spice Source' )
,       ( 3, N'Berbere', N'International Supply' )
,       ( 4, N'Bumbu', N'Natural Farms' )
,       ( 5, N'Chaat masala', N'Sells All' )
,       ( 6, N'Chili powder', N'Jo''s Stuff' )
,       ( 9, N'Curry powder', N'VJC Processing' );

SELECT * FROM [dbo].[Spices];
SELECT * FROM [dbo].[Spices_New];
GO

Now, suppose you want to synchronize the dbo.Spices target table with the dbo.Spices_New source table. Here is the criterion for this task:

  • Spices that exist in both the dbo.Spices_New source table and the dbo.Spices target table are updated in the dbo.Spices target table with new names
  • Any spices in the dbo.Spices_New source table that do not exist in the dbo.Spices target table are inserted into the dbo.Spices table target table
  • Any spices in the dbo.Spices target table that do not exist in the dbo.Spices_New source table must be deleted from the dbo.Spices target table

Without the MERGE statement, one has to write multiple DML statements to accomplish this task. Moreover, for each DML operation, SQL Server processes the data separately, resulting in more time taken to complete each task. However, with the MERGE statement, you can perform this task in a single statement. Here is the MERGE statement to perform this task:

USE [AdventureWorks2012];
GO

MERGE [dbo].[Spices] AS [SourceTbl]
USING [dbo].[Spices_New] AS [TargetTbl]
ON ( SourceTbl.[SpiceID] = TargetTbl.[SpiceID] )
WHEN MATCHED AND SourceTbl.[SpiceMixName] <> TargetTbl.[SpiceMixName]
    OR SourceTbl.[Supplier] <> TargetTbl.[Supplier] THEN
    UPDATE SET SourceTbl.[SpiceMixName] = TargetTbl.[SpiceMixName] ,
               SourceTbl.[Supplier] = TargetTbl.[Supplier]
WHEN NOT MATCHED THEN
    INSERT ( [SpiceID] ,
             [SpiceMixName] ,
             [Supplier]
           )
    VALUES ( TargetTbl.[SpiceID] ,
             TargetTbl.[SpiceMixName] ,
             TargetTbl.[Supplier]
           )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT
    $action ,
    INSERTED.* ,
    DELETED.*;
GO

The following screenshot shows the rows affected by this MERGE statement:

The MERGE statement examples
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.47.166