Merge Statement with insert into output table

    DECLARE @tblMergeOutput TABLE
    (
        ID                BIGINT IDENTITY(1,1)
        , MergeAction    NVARCHAR(100)
        , InsertedId    INT
        , DeletedId        INT
        , GroupID        INT
    )

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    INSERT INTO #tblSource VALUES (4,1,'a41')

    INSERT INTO #tblTarget VALUES (1,1,'a1')
    INSERT INTO #tblTarget VALUES (3,1,'a3')
    INSERT INTO #tblTarget VALUES (5,1,'a5')
    INSERT INTO #tblTarget VALUES (6,2,'b2')
    INSERT INTO #tblTarget VALUES (7,2,'b1')

    
    ---- Method 2 : Using Output insert into table
    BEGIN
        INSERT INTO @tblMergeOutput
            SELECT MergeAction, InsertedId, DeletedID, GroupID 
            FROM
            (
                MERGE #tblTarget
                USING (
                        SELECT *
                        FROM #tblSource
                    ) AS tblSource
                        ON tblSource.GroupID = #tblTarget.GroupID
                            AND tblSource.ID = #tblTarget.ID
                WHEN MATCHED THEN 
                    UPDATE
                    SET Name = tblSource.Name
                WHEN NOT MATCHED THEN
                    INSERT (
                                ID
                                , GroupID
                                , Name
                            )
                    VALUES ( 
                                tblSource.ID
                                , tblSource.GroupID
                                , tblSource.Name
                            )
                WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
                    THEN DELETE
                OUTPUT 
                    $action                AS MergeAction
                    , inserted.Id        AS InsertedId
                    , deleted.Id        AS DeletedID
                    , tblSource.GroupID    AS GroupID
            ) AS MergeOutput (MergeAction, InsertedId, DeletedID, GroupID);
    END

    SELECT * FROM #tblTarget order by GroupID, ID
    SELECT * FROM @tblMergeOutput

    DROP TABLE #tblSource
    DROP TABLE #tblTarget