Capturing Insert ,Update and Delete counts from Merge

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.

Solution

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';