====== MERGE boilerplate ======

T-SQL provides upsert capability via the ''MERGE'' statement. When merging from a source result set to a target result set (based on a given join criterion) you can specify, :
  * action to be taken if a row in the source matches a row in the target;
  * action to be take if a row in the source has no match in the target;
  * action to be take if a row in the target has no match in the source.

===== Boilerplate =====

This is the basic structure of a ''MERGE'' statement that updates matching records, inserts missing ones and deletes any no longer present at source. 

For clarity, source and target are aliased as ''src'' and ''tgt'' -- this could be directly or via CTEs if it is necessary either to derive the source result set or to limit the scope of the target.

We assume the existence of a ''LastUpdated'' DATETIME NOT NULL field in the target table that defaults to ''GETDATE()'' (so the field need not be populated in the ''INSERT'' clause).

==== Physical deletion ====

<code tsql>MERGE INTO tgt
USING src
  ON
WHEN MATCHED AND (
) THEN
  UPDATE
  SET 
    , LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (
  ) VALUES (
  )
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
;
</code>

==== Logical deletion ====

For logical deletion, we further assume the existence of an ''IsDeleted'' BIT NOT NULL field in the target table that defaults to ''0'' (so the field need not be populated in the ''INSERT'' clause).

<code tsql>MERGE INTO tgt
USING src
  ON
WHEN MATCHED AND (
) THEN
  UPDATE
  SET 
    , IsDeleted = 0
    , LastUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
  INSERT (
  ) VALUES (
  )
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE
  SET IsDeleted = 1
    , LastUpdated = GETDATE()
;
</code>

===== Generating clause content =====
This script generates some possible content for each of the MATCHED/NOT MATCHED clauses above. The content of ''WHEN MATCHED AND()'' is intended to limit the update to true changes such that matched rows are only updated when a difference is found.

<code tsql>DECLARE @tableName NVARCHAR(257) = 'dbo.Customers'
 
WITH cte AS (
  SELECT
    '  AND src.[' + c.name + '] = tgt.[' + c.name + ']' AS JoinClause
  , '  OR src.[' + c.name + '] <> tgt.[' + c.name + ']
  OR src.[' + c.name + '] IS NULL AND tgt.[' + c.name + '] IS NOT NULL
  OR src.[' + c.name + '] IS NOT NULL AND tgt.[' + c.name + '] IS NULL' AS MatchClause
  -- to follow (matched) 'UPDATE SET...':
  , '    , [' + c.name + '] = src.[' + c.name + ']' AS UpdateClause
  -- to follow 'INSERT (...':
  , '  , [' + c.name + ']' AS InsertTarget
  -- to follow ') VALUES (...':
  , '  , src.[' + c.name + ']' AS InsertSource
  , pkc.key_ordinal
  , c.column_id
  FROM sys.schemas s
    INNER JOIN sys.objects o ON o.[schema_id] = s.[schema_id]
    INNER JOIN sys.columns c ON c.[object_id] = o.[object_id]
    LEFT JOIN sys.indexes pk ON pk.[object_id] = o.[object_id] AND pk.is_primary_key = 1
    LEFT JOIN sys.index_columns pkc
      ON pkc.index_id = pk.index_id
      AND pkc.[object_id] = pk.[object_id]
      AND pkc.column_id = c.column_id
  WHERE o.[object_id] = OBJECT_ID(@tableName)
)
SELECT  
  CASE WHEN key_ordinal IS NOT NULL THEN JoinClause END  -- to follow USING src ON
, CASE WHEN key_ordinal IS NULL THEN MatchClause END     -- to follow WHEN MATCHED AND (
, CASE WHEN key_ordinal IS NULL THEN UpdateClause END    -- to follow matched UPDATE SET
, InsertTarget                                           -- to follow INSERT (
, InsertSource                                           -- to follow ) VALUES (
FROM cte
ORDER BY COALESCE(key_ordinal, column_id * 100)</code>

{{tag> tsql development}}