Utiliser une procédure de MERGE dans une logique ELT pour SQL

-- Creation of the Technical Table to follow up the insert/update of the Target table 
IF NOT EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME = 'TCH_MERGE_FOLLOWUP')
CREATE TABLE TCH_MERGE_FOLLOWUP (
	ID_run				INT IDENTITY(1,1) NOT NULL, 
	LB_MergeAction		NVARCHAR(20) NOT NULL,
	IN_ActionCount		INT NOT NULL, 
	LB_MergeCallInfo	NVARCHAR(50) NULL,
	DT_MergeDateTime	DATETIME DEFAULT GETDATE()
);

CREATE OR ALTER PROCEDURE dbo.MERGE_SRC_TGT
    @TchInfo NVARCHAR(50)											-- Parameter to keep origin of the Merge Execution 
AS
BEGIN  
    SET NOCOUNT ON;  

	DECLARE @RunDate DATETIME										-- RunDate calculated at the begining of the run to follow up merge 
	SET @RunDate = GETDATE()										-- This Date will be used to read last inserted values in our source later 

	DECLARE @TMP_MergeAction TABLE(MergeAction VARCHAR(20));		-- Table to track list of actions done during last run 

    MERGE dbo.TGT AS target											-- target table that will be inserted/merged into 
    USING (
		SELECT [src_name]
			  ,[amount]
			  ,[src_date] 
		FROM dbo.TEMP_TGT) AS temp ([src_name],[amount],[src_date]) -- definition of the fields that will be read from the source 
	ON (target.[name] = temp.[src_name])							-- definition of the keys that will match/unmatch data 

    WHEN MATCHED THEN
        UPDATE SET [amount] = temp.[amount]							-- if the keys match, it is an existing row, then update
    WHEN NOT MATCHED THEN 
        INSERT ([name],[amount],[src_date])  
        VALUES (temp.[src_name],temp.[amount],temp.[src_date])		-- if the keys don't match, it is a new row, then insert 
	OUTPUT $action INTO @TMP_MergeAction;							-- all the output metadata are stored in a our tracklist table

INSERT INTO TCH_MERGE_FOLLOWUP (LB_MergeAction, IN_ActionCount, LB_MergeCallInfo, DT_MergeDateTime)
	SELECT MergeAction, COUNT(MergeAction) AS CountPerChange, @TchInfo, @RunDate
	FROM @TMP_MergeAction											-- summarize result in tracklist table. 
	GROUP BY MergeAction;  

END;  
GO  

-- Sample Load of the Temporary table
INSERT INTO dbo.TEMP_TGT
SELECT [src_name]
      ,[amount]
      ,[src_date]
  FROM [dbo].[SRC]
  WHERE [src_date] > (SELECT MAX(DT_MergeDateTime) FROM TCH_MERGE_FOLLOWUP)

-- Sample Exec of the merge statement
EXEC dbo.MERGE_SRC_TGT 'Demo Run'
TRUNCATE TABLE dbo.TEMP_TGT