Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tsql:merge_boilerplate [2017/09/20 14:23] (current)
richard created
Line 1: Line 1:
 +====== 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}}
 +~~DISCUSSION~~
 +