Sql-Server

我可以在 WHEN MATCHED 條件下簡化這個 MERGE 語句嗎？

  • November 5, 2020

我正在使用 SQL Server（SQL Server 2016 和 Azure SQL）並且我有這個MERGE語句，它使用一個相當粗糙的WHEN MATCHED條件來只更新值實際上不同的行。

這樣做有兩個原因：

  1. 該表有一rowversion列在UPDATE執行操作時會發生變化，即使所有值都相同。這些rowversion值對於減少客戶端活動（應用程序rowversion用於樂觀並發）很有用。
  2. 該表也是一個臨時表，並且 SQL Server 的臨時表實現將在執行時將實時數據的副本添加到歷史表UPDATE中，即使實際上沒有更改任何值。
CREATE PROCEDURE UpsertItems
   @groupId int,
   @items   dbo.ItemsList READONLY -- This is a table-valued parameter. The UDT Table-Type has the same design as the `dbo.Items` table.

WITH existing AS    -- Using a CTE as the MERGE target to allow *safe* use of `WHEN NOT MATCHED BY SOURCE THEN DELETE` and apparently it's good for performance.
(
   SELECT
       groupId,
       itemId,
       a,
       b,
       c,
       d,
       e,
       f,
       -- etc
   FROM
       dbo.Items
   WHERE
       groupId = @groupId
)
MERGE INTO existing WITH (HOLDLOCK) AS tgt
USING
   @items AS src ON tgt.itemId = src.itemId
WHEN MATCHED AND
(
   -- This part is painful, but unfortunately these are all NULLable columns so they need the full `x IS DISTINCT FROM y`-equivalent comparison:

   ( ( tgt.a <> src.a OR tgt.a IS NULL OR src.a IS NULL ) AND NOT ( tgt.a IS NULL AND src.a IS NULL ) )
   OR
   ( ( tgt.b <> src.b OR tgt.b IS NULL OR src.b IS NULL ) AND NOT ( tgt.b IS NULL AND src.b IS NULL ) )
   OR
   ( ( tgt.c <> src.c OR tgt.c IS NULL OR src.c IS NULL ) AND NOT ( tgt.c IS NULL AND src.c IS NULL ) )
   OR
   ( ( tgt.d <> src.d OR tgt.d IS NULL OR src.d IS NULL ) AND NOT ( tgt.d IS NULL AND src.d IS NULL ) )
   OR
   ( ( tgt.e <> src.e OR tgt.e IS NULL OR src.e IS NULL ) AND NOT ( tgt.e IS NULL AND src.e IS NULL ) )
   OR
   ( ( tgt.f <> src.f OR tgt.f IS NULL OR src.f IS NULL ) AND NOT ( tgt.f IS NULL AND src.f IS NULL ) )
   -- etc
)
THEN UPDATE SET
   tgt.a = src.a,
   tgt.b = src.b,
   tgt.c = src.c,
   tgt.d = src.d,
   tgt.e = src.e,
   tgt.f = src.f,
   -- etc
WHEN NOT MATCHED BY TARGET THEN INSERT (
   groupId,
   itemId,
   a,
   b,
   c,
   d,
   e,
   f,
   -- etc
)
VALUES (
   src.groupId,
   src.itemId,
   src.a,
   src.b,
   src.c,
   src.d,
   src.e,
   src.f,
   -- etc
)
WHEN NOT MATCHED BY SOURCE THEN DELETE

OUTPUT
   $action AS [Action],

   inserted.groupId AS Ins_groupId,
   deleted .groupId AS Del_groupId,
   inserted.itemId  AS Ins_itemId,
   deleted .itemId  AS Del_itemId,
   inserted.a       AS Ins_a,
   deleted .a       AS Del_a,
   inserted.b       AS Ins_b,
   deleted .b       AS Del_b,
   inserted.c       AS Ins_c,
   deleted .c       AS Del_c,
   inserted.d       AS Ins_d,
   deleted .d       AS Del_d,
   inserted.e       AS Ins_e,
   deleted .e       AS Del_e,
   inserted.f       AS Ins_f,
   deleted .f       AS Del_f,
   -- etc
;

如您所見，這是放棄維護的痛苦！

我已經使用像 T4 這樣的工具來自動生成這個查詢的重複部分，但是這個語句的純粹……規模和痛苦MERGE讓我覺得我在做一些非常錯誤的事情（因為軟體是為了照亮道路通過成功的深淵，所以如果一個人在嘗試做正確的事情時遇到困難，你可能做錯了），但我想不出或看到更好的方法來實現這一點（BULK INSERT儘管如此，但為了這個目的不可能的問題）。

我知道這個語句可以在其他支持的 RDBMS 中被簡化x IS DISTINCT FROM y（它取代了 中可怕但必要NULL的安全檢查WHEN MATCHED AND，但 SQL Server仍然不支持它。

另一個痛點是 SQL 通常缺乏 DRY - 以及在 SQL Server 中實現 DRY 數據庫的困難（例如，不支持延遲約束或表繼承，因此您無法實現子類表模式，這意味著不必要的重複多個表中的數據設計和較弱的約束） - 但這是另一個主題。與 Kotlin 和 TypeScript 等現代語言中的許多節省時間和節省按鍵的功能相比，今天的 SQL 程式看起來多麼落後，我對此感到沮喪。

幻想時間：

我希望能夠做這樣的事情，而不是與任何陷阱有關（比如MERGE預設情況下沒有明確的不安全HOLDLOCK- 這太瘋狂了！）：

MERGE INTO
   dbo.Items AS tgt
WHERE
   tgt.groupId = @groupId
FROM
   @items AS src
ON
   tgt.itemId = src.itemId
WHEN MATCHED AND DIFFERENT THEN UPDATE ( automap )
WHEN NOT MATCHED BY TARGET THEN INSERT ( automap )
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT ALL;

（如果automapSQL Server 不能自動安全地將列相互映射，則會自動按名稱映射源列和目標列，並引發編譯時錯誤），並OUTPUT ALL輸出具有不同列的 all $action、inserted和deletedvalues名稱 - 使用相同的列名，但在相鄰行中具有inserted和值）。deleted

正如 Brian 所指出的，使用 ofEXCEPT是最好的選擇，但這可以直接在MERGE如下使用：

WHEN MATCHED AND EXISTS(
   SELECT src.*
   EXCEPT 
   SELECT tgt.*
   )

這是一個完整的工作範例：

DROP TABLE IF EXISTS #Test
CREATE TABLE #TEST (
   A INT PRIMARY KEY,
   B INT, 
   C INT,
)

INSERT INTO #Test 
   VALUES (1, 1, 1)
       ,(2, 1, 1)
       ,(3, 1, 1)
       ,(4, 1, NULL)
       ,(5, 1, NULL)
       ,(6, 1, NULL)

;WITH NewValues AS (
   SELECT * FROM (
       VALUES (2, 0, 1) --Update
           ,(3, 1, 1) -- Do Nothing
           ,(4, 0, NULL) -- UPDATE
           ,(5, 1, NULL) -- Do nothing, NULL = NULL
           ,(6, 1, 1) -- UPDATE 
           ,(7, 1, 1) -- INSERT
   ) V(A, B, C)
)
MERGE INTO #Test As t
USING 
   NewValues AS S ON t.A = s.A
WHEN MATCHED AND EXISTS(
   -- New values Exist Expcet when the match the old values exactly
   SELECT s.B, s.C 
   EXCEPT 
   SELECT t.B, t.C
   )
THEN UPDATE SET
   t.B = s.B,  t.C = s.C
WHEN NOT MATCHED BY TARGET THEN INSERT(
   A,  B,  C
) VALUES (s.A, s.B, s.C)
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT
   $action AS [Action],
   inserted.*,
   deleted.*;

SELECT * FROM #TEST

一種選擇是單獨進行刪除並使用EXCEPT查詢獲取要添加或更新的數據。

SELECT a,b,c,d,e,f FROM SOURCE
EXCEPT
SELECT a,b,c,d,e,f FROM DESTINATION

引用自：https://dba.stackexchange.com/questions/268624

相關問答

Sql-Server

在合併中使用 Where 子句

  • June 9, 2020
檢視問答
Sql-Server

在合併更新語句中使用聚合

  • May 7, 2020
檢視問答
Sql-Server

幫助合併聲明

  • September 10, 2018
檢視問答
Sql-Server

將表輸出合併到另一個表的列中

  • April 4, 2018
檢視問答
Sql-Server

MERGE 最佳實踐的說明

  • January 11, 2018
檢視問答
Sql-Server

與有條件的 INSERT 和 SELECT 相比，帶有 OUTPUT 的 MERGE 是更好的做法嗎？

  • December 22, 2016
檢視問答