UPSERT - CAPTURE UPDATED ROWS INTO ANOTHER AUDIT TABLE

Question

Hi there,

I'm trying to do soft delete on a target table which are not in source. Basically if the ID is not in source but ID is in target, update that RecordStatusKey of that specific target ID.

I would like to capture the records updated in merge. Is that possible?

	DECLARE @MergeOutputTable TABLE
	([Action] varchar(20), 
	 [Id] bigint
	)  
    -- Execute the Merge for Insert and Update    
	MERGE INTO [dbo].[TargetTbl] AS T 
	
	USING [dbo].[SourceTbl] AS S 
	
	ON T.[Id] = S.[Id]
	WHEN NOT MATCHED BY SOURCE THEN
	UPDATE SET 
	T.[RecordStatusKey]			= 2
	
	Would like to capture the records that got updated into a Audit table.
	--INSERT INTO [Audit].[TrackALLDeletions] --how can i do this?
	--VALUE (T.[Id], getdate() DateOfDeletion)

	OUTPUT $action, INSERTED.[Id]
	INTO @MergeOutputTable;
 -- Capture row counts
	SELECT 
	    @RowsWritten = SUM(CASE WHEN [action] = 'INSERT' THEN 1 ELSE 0 END),
	    @RowsUpdated = SUM(CASE WHEN [action] = 'UPDATE' THEN 1 ELSE 0 END),
	    @RowsDeleted = SUM(CASE WHEN [action] = 'DELETE' THEN 1 ELSE 0 END)
	FROM @MergeOutputTable;

Answer 1

Your @mergeOutputTable contains the records you updated.