Get ROWCOUNT for all actions performed in MERGE Statement

Question

I amazed myself with this MERGE statement, the company isn't truly doing a Type 2 Slowing Changing Dimension but close. Oddly it's not even analytical data but let's ignore that horrendous decision. I have this working referencing HashBytes to indicated changed rows. Unfortunately, to get all scenarios addressed I ended up with that additional INSERT at then end from the temp table which actually holds the updated rows.

Alas it's functional but if you have a more effective design, please do share. I would appreciate it.

However, I am attempting to get a row count representing not only for the INSERT from the Temp table, but the updates AND the new INSERTS, all are distinct separate actions with their own row count, that I need to document and account for.

How can I do this, please ?

DECLARE @dtNow AS DATETIME = GetDate()
DECLARE @dtPast AS DATETIME = DATEADD(day,-1,GetDate())
DECLARE @dtFuture AS DATETIME = '22991231'
    SET NOCOUNT ON;

  -- Temp Table is JUST Updating Rows reflecting 
--Historical Marker on existing row No content change to row's columnar     content data 

IF OBJECT_ID('tempdb..#TheTempTableName') IS NOT NULL DROP TABLE     #TheTempTableName


CREATE TABLE #TheTempTableName 
(
    ABunchOfColumns
    RowCreatedDate datetime NULL,
    RowEffectiveDate datetime NULL,
    RowTerminationDate datetime NULL,
    RowIsCurrent bit NULL,
    RowHash varchar(max) NULL,
)

INSERT INTO #TheTempTableName
(
            ABunchOfColumns
                ,RowCreatedDate
                ,RowEffectiveDate
                ,RowTerminationDate
                ,RowIsCurrent
                ,RowHash 
)


SELECT
                ABunchOfColumns
                ,RowCreatedDate
                ,RowEffectiveDate
                ,RowTerminationDate
                ,RowIsCurrent
                ,RowHash 
FROM
    (
        MERGE tblDim WITH (HOLDLOCK) AS target
            USING 
            (
                SELECT          
                ABunchOfColumns
                ,RowCreatedDate
                ,RowEffectiveDate
                ,RowTerminationDate
                ,RowIsCurrent
                ,RowHash 

                FROM dbo.tblStaging
            )       
        AS source
        ON target.PKID  = source.PKID       
        WHEN MATCHED
            AND target.RowIsCurrent = 1
            AND target.RowHash != source.RowHash                    
------- PROCESS ONE -- UPDATE --- HISTORICALLY MARK EXISTING ROWS
            THEN UPDATE SET                     
                    RowEffectiveDate    =   @dtPast
                    ,RowTerminationDate =   @dtPast
                    ,RowIsCurrent   =   0                   
-----  PROCESS TWO -- INSERT ---INSERT NEW ROWS
            WHEN NOT     MATCHED                                                          
            THEN INSERT    ---   THIS INSERT Goes directly into Target ( DIM     ) Table (New Rows not matched with PK = PK ) 
                (
                ABunchOfColumns
                ,RowCreatedDate
                ,RowEffectiveDate
                ,RowTerminationDate
                ,RowIsCurrent
                ,RowHash 
                )
                VALUES
                (                   
                source.ABunchOfColumns
                    ,@dtNow                 --source.RowCreatedDate,
                    ,@dtFuture              ---source.RowEffectiveDate,
                    ,@dtFuture              ---source.RowTerminationDate,
                    ,1                      ---source.RowIsCurrent,
                    ,source.RowHash
                )
-------PROCESS THREE a  -- INSERT ---OUTPUT MATCHED ROWS FROM PROCESS ONE THAT CAUSED     HISTORICAL MARK (CHANGES) "INSERT"
        OUTPUT 
        $action Action_Out,

                ABunchOfColumns
                ,RowCreatedDate
                ,RowEffectiveDate
                ,RowTerminationDate
                ,RowIsCurrent
                ,RowHash 
    )       
    AS MERGE_OUT
WHERE MERGE_OUT.Action_Out = 'UPDATE';

----------PROCESS THREE b  -- INSERT FROM Temp Tbl to final
--Now we flush the data in the temp table into dim table

INSERT INTO tblDim
    (
                ABunchOfColumns
                ,RowCreatedDate
                ,RowEffectiveDate
                ,RowTerminationDate
                ,RowIsCurrent
                ,RowHash 
    )
SELECT
         ABunchOfColumns
        ,@dtNow AS RowCreatedDate
        ,@dtFuture AS RowEffectiveDate
        ,@dtFuture AS RowTerminationDate
        ,1 AS RowIsCurrent
        ,RowHash

FROM #TheTempTableName  
END

Answer 1

There are two types of deletes (1) real deletes (2) primary key updates. So you can also say there are two types of inserts (1) real inserts (2) primary key updates The updates are always updates.

The dilemma then is when is an insert/delete combination is really an update.

Usually if you dont really care about the one above a simple merge like this is sufficient

MERGE esqlProductTarget T
USING esqlProductSource S
ON (S.ProductID = T.ProductID)
WHEN MATCHED
THEN UPDATE
     SET    T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT S.ProductID, $action into @MergeLog;

SELECT MergeAction, Cnt=count(*)
FROM   @MergeLog
GROUP BY MergeAction

The output will be like:

+-------------+-----+--+
| MergeAction | Cnt |  |
+-------------+-----+--+
| DELETE      | 100 |  |
| UPDATE      |  60 |  |
| INSERT      |  70 |  |
+-------------+-----+--+

Refer to https://www.essentialsql.com/introduction-merge-statement/

I am not sure why you have "WHERE MERGE_OUT.Action_Out = 'UPDATE'. But if you remove that, then you can get your rowcount. Unless I have misunderstood your query.