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