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

MERGE Statement and compatibility level

In SQL 2008 Merge Statement when execute and give the error like this

Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

Solution
SQL Command

ALTER DATABASE <DBName>
SET COMPATIBILITY_LEVEL = 100 

Merge Statement

SQL Statement:

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

 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
  , inserted.Id
  , deleted.Id
  , tblSource.GroupID
 INTO @tblMergeOutput;

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

 DROP TABLE #tblSource
 DROP TABLE #tblTarget

Target Table Output:

 ------------------
 ID  GroupID   Name
 ------------------
 1   1         a11
 2   1         a21
 3   1         a31
 4   1         a41
 6   2         b2
 7   2         b1
 ------------------

Merge Table Output:

 ----------------------------------------------------
 ID  Merge Action    InsertedID   DeletedID   GroupID
 ----------------------------------------------------
 1   INSERT          2            NULL        1
 2   INSERT          4            NULL        1
 3   UPDATE          1            1           1
 4   UPDATE          3            3           1
 5   DELETE          NULL         5           NULL
 ----------------------------------------------------

Merge Query W/O Using Merge

DECLARE @tblSource Table ( ID INT, GroupID INT, Name VARCHAR(10) )
DECLARE @tblTarget Table ( ID INT, GroupID INT, Name VARCHAR(10) )

INSERT INTO @tblSource VALUES (1,1,'a1')
INSERT INTO @tblSource VALUES (2,1,'a2')
INSERT INTO @tblSource VALUES (3,1,'a3')
INSERT INTO @tblSource VALUES (4,1,'a4')

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

-----Delete Record
SELECT * 
FROM @tblSource AS tblSource
 FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NULL
 AND tblTarget.ID IS NOT NULL
 AND tblTarget.GroupID = 1
 
-----Update Record
SELECT * 
FROM @tblSource AS tblSource
 FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE tblSource.ID IS NOT NULL
 AND tblTarget.ID IS NOT NULL

-----Inserted Record
SELECT * 
FROM @tblSource AS tblSource
 FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID
WHERE 
 tblSource.ID IS NOT NULL
 AND tblTarget.ID IS NULL
 AND tblSource.GroupID = 1

--SELECT * 
--FROM @tblSource AS tblSource
-- LEFT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT * 
--FROM @tblSource AS tblSource
-- RIGHT OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID

--SELECT * 
--FROM @tblSource AS tblSource
-- FULL OUTER JOIN @tblTarget AS tblTarget ON tblTarget.ID = tblSource.ID