如何完成這種插入或更新策略？

程式儲存過程並不是我在程式方面的強項，但我正在努力變得更好。

我偶然發現了一個對我來說有點困難的業務需求，我想知道是否有人已經在他們的職業生涯中處理過它。

暫存表中有這些數據需要載入到生產表中，其中插入用於新數據，插入更新（類似備份）用於舊數據。

生產表中的數據樣本如下所示：

同時在舞台上：

在 prod 中，PK 是AssetID同時BeginDate在 staging 中是唯一AssetID的（因為每次都會刷新數據）。

要求是編寫一個儲存過程，當暫存區有新資產時，將它們作為新資產插入到 prod（帶有BeginDateasCURRENT_TIMESTAMP和EndDateas ）中，同時如果, or欄位NULL上有不同的數據（在暫存中）從生產記錄開始，然後用相同但as和as寫入新記錄，並用as更新具有相同組合（記憶和is ）的舊記錄Field1``Field2``FieldN``AssetID``BeginDate``CURRENT_TIMESTAMP``EndDate``NULL``BeginDate``EndDate``NULL``EndDate``CURRENT_TIMESTAMP

你們中有人處理過類似的任務/要求嗎？如果是，你能分享你的解決方案嗎？

在插入新記錄之前，您必須嘗試最終確定給定 AssetID 的任何現有打開記錄。

BEGIN
 DECLARE ts TIMESTAMP = NOW();
 DECLARE f1, f2, fN FLOAT; 

 SELECT Field1, Field2, FieldN
   FROM prod_table
  WHERE AssetID = _givenID
    AND EndDate = NULL
   INTO f1, f2, fN
 ;

 IF ( f1 <> _field1 OR 
      f2 <> _field2 OR 
      fN <> _fieldN )
   THEN 
     UPDATE prod_table    -- Here we suppose there is zero or one opened record
        SET EndDate = ts  -- If there is no records for _givenID, update do nothing
      WHERE AssetID = _givenID
        AND EndDate IS NULL  
     ;

     INSERT prod_table (AssetID, BeginDate, EndDate, Field1, Field2, FieldN)
     VALUES ( _givenID, ts, NULL, _field1, _field2, _fieldN )
     ;
 END IF;
END

您可以使用帶有 OUTPUT 子句的MERGE語句來實現此目的。基本上，MERGE 語句將暫存表中的每條記錄與數據表中的記錄進行比較。找到匹配項時，它會更新現有記錄以輸入結束日期值，如果找不到匹配項，則插入記錄。

OUTPUT 子句用於在發生 UPDATE 時輸出記錄，整個 MERGE 語句被包裹在 INSERT 語句中。這允許在找到匹配項時插入新記錄，因為 MERGE 不允許多個語句。

程式碼：

 INSERT INTO Data
 SELECT AssetID, BeginDate, EndDate, Value
 FROM
 (
   MERGE INTO Data as target
   USING Stage as source ON (source.AssetID = target.AssetID)
   WHEN MATCHED AND target.Value <> source.Value THEN
     UPDATE SET EndDate = GETDATE()
   WHEN NOT MATCHED THEN
     INSERT (AssetID, BeginDate, EndDate, Value)
     VALUES (source.AssetID, GETDATE(), NULL, source.Value)
   OUTPUT $action AS Action, Source.AssetID AS AssetID, GETDATE() AS BeginDate, NULL AS EndDate, source.Value AS Value
 )
 AS Changes (Action, AssetID, BeginDate, EndDate, Value) WHERE Action = 'UPDATE'

db<>fiddle 上的工作範例

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