Capturing Insert ,Update and Delete counts from Merge

Question

I use the below T-SQL Merge statement to perform incremental load data from transactional database into Data Warehouse. This Merge statement loads data as a SCD Type 2 and it works well.

--Begin handling SCD2 changes
INSERT INTO  [DWDB].[dbo].[Dim_Warehouse]      
    (
         stg.[WarehouseID]
        ,stg.[WarehouseCode]
        ,stg.[WarehouseName]
    )   
SELECT 
         MERGE_OUT.[WarehouseID]
        ,MERGE_OUT.[WarehouseCode]
        ,MERGE_OUT.[WarehouseName]
FROM     
    (
        MERGE [DWDB].[dbo].[Dim_Warehouse] AS stg
        USING SourceDB.dbo.Warehouse AS SRC    
        ON (stg.WarehouseID = SRC.WarehouseID) 
    WHEN NOT MATCHED     
        THEN INSERT VALUES
            (
             SRC.[WarehouseID]
            ,SRC.[WarehouseCode]
            ,SRC.[WarehouseName]
            )
    WHEN MATCHED
        AND stg.dw_EndDate IS NULL
        AND 
            (
              stg.[WarehouseName]   <> src.[WarehouseName]
            ) 

        THEN
            UPDATE 
            SET  stg.dw_EndDate =  GETDATE()
                ,stg.dw_IsCurrent = 0 
    OUTPUT $action Action_Out
            ,SRC.[WarehouseID]
            ,SRC.[WarehouseCode]
            ,SRC.[WarehouseName]
            ) AS MERGE_OUT
WHERE MERGE_OUT.Action_Out = 'UPDATE';

(some columns were removed from the above source code to make it simple)

My final work is to count on MERGE Statement to know how many records were Inserted, Updated, Deleted but not successful.

I have tried to create a temp table and put columns in the OUTPUT as these links suggest: Sql Server 2008 MERGE - best way to get counts and https://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/ but it looks different from my case.

Any your help is greatly appreciated.

What does "it looks different from my case" mean? Does it mean the results aren't what you expect? . Does this help? https://www.sqlservercentral.com/articles/the-output-clause-for-the-merge-statements — Nick.Mc, Jul 06 '21 at 04:22
Hi Nick.McDermaid, Thank you for your reply. Normally we use Merge....Output Inserted.*, Deleted.* into a temp table (for example temp table @archive as the link you suggest). But my case, I cann't use this way because after OUTPUT, I use ") AS MERGE_OUT WHERE MERGE_OUT.Action_Out = 'UPDATE'" as you see. Can you teach me how to use a temp table to store how many Inserts, Updates, Deletes for this my Merge? — Peter Nguy Nguyen, Jul 07 '21 at 04:43

Nick.Mc · Accepted Answer · 2021-07-07T08:02:11.143

Just to get terminology right, @archive is a table variable, not a temp table. (they are different in many ways). A temp table looks like this #archive

To solve your problem you can simply load into a table variable as normal then insert from that table variable afterwards. This activity is in two steps. It doesn't need to all be done in one step.

Also FYI, it's not necessary to use merge at all, you can use seperate insert/update statements. A lot of people don't realise this. Also keep in mind this list of issues with merge. They are mostly edge cases but keep in mind that there are other options

https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

      -- Create the table variable to capture the output
      DECLARE @MergeOutput (
            Action_Out VARCHAR(100),
            [WarehouseID] INT,
            [WarehouseCode] VARCHAR(50),
            [WarehouseName] VARCHAR(50)
      );


        -- mere into table, capturing output into table variable
        MERGE [DWDB].[dbo].[Dim_Warehouse] AS stg
        USING SourceDB.dbo.Warehouse AS SRC    
        ON (stg.WarehouseID = SRC.WarehouseID) 
        WHEN NOT MATCHED     
        THEN INSERT VALUES
            (
             SRC.[WarehouseID]
            ,SRC.[WarehouseCode]
            ,SRC.[WarehouseName]
            )
    WHEN MATCHED
        AND stg.dw_EndDate IS NULL
        AND 
            (
              stg.[WarehouseName]   <> src.[WarehouseName]
            ) 

        THEN
            UPDATE 
            SET  stg.dw_EndDate =  GETDATE()
                ,stg.dw_IsCurrent = 0 
    OUTPUT $action Action_Out
            ,SRC.[WarehouseID]
            ,SRC.[WarehouseCode]
            ,SRC.[WarehouseName]
    INTO @MergeOutput

-- Perform insert based on table variable
INSERT INTO  [DWDB].[dbo].[Dim_Warehouse]      
    (
         [WarehouseID]
        ,[WarehouseCode]
        ,[WarehouseName]
    )   
SELECT 
         [WarehouseID]
        ,[WarehouseCode]
        ,[WarehouseName]
FROM @MergeOutput
WHERE Action_Out = 'UPDATE';

Capturing Insert ,Update and Delete counts from Merge

1 Answers1