使用 xml 參數插入多個數據時如何避免使用合併查詢？

我正在嘗試使用一組值更新表。數組中的每一項都包含與 SQL Server 數據庫中表中的行匹配的資訊。如果該行已經存在於表中，我們使用給定數組中的資訊更新該行。否則，我們在表中插入一個新行。我基本上已經描述了upsert。

現在，我試圖在一個採用 XML 參數的儲存過程中實現這一點。我使用 XML 而不是表值參數的原因是，在執行後者時，我必須在 SQL 中創建自定義類型並將此類型與儲存過程相關聯。如果我在以後更改了儲存過程或數據庫模式中的某些內容，我將不得不重做儲存過程和自定義類型。我想避免這種情況。此外，TVP 對 XML 的優勢對我的情況沒有用處，因為我的數據數組大小永遠不會超過 1000。這意味著我不能使用這裡提出的解決方案：How to insert multiple records using XML in SQL server 2008

此外，此處的類似討論（UPSERT - Is there a better alternative to MERGE or @@rowcount?）與我所要求的不同，因為我試圖將多行插入到表中。

我希望我會簡單地使用以下一組查詢來更新 xml 中的值。但這行不通。當輸入是單行時，這種方法才應該起作用。

begin tran
  update table with (serializable) set select * from xml_param
  where key = @key

  if @@rowcount = 0
  begin
     insert table (key, ...) values (@key,..)
  end
commit tran

下一個替代方法是使用詳盡的 IF EXISTS 或其以下形式的變體之一。但是，我以效率次優為由拒絕了這一點：

IF (SELECT COUNT ... ) > 0
   UPDATE
ELSE
   INSERT

下一個選項是使用此處描述的 Merge 語句：http ://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html 。但是，然後我在這裡閱讀了有關合併查詢的問題：http ://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ 。出於這個原因，我試圖避免合併。

所以，現在我的問題是：在 SQL Server 2008 儲存過程中使用 XML 參數實現多個 upsert 是否有任何其他選項或更好的方法？

請注意，XML 參數中的數據可能包含一些不應該被 UPSERTed 的記錄，因為它們比目前記錄更舊。XML 和目標表中都有一個ModifiedDate欄位需要進行比較，以確定是否應該更新或丟棄記錄。

源是 XML 還是 TVP 並沒有太大的區別。整體操作本質上是：

1. 更新現有行
2. 插入缺失的行

您按此順序執行此操作，因為如果您先插入，則存在所有行以獲取更新，並且您將對剛剛插入的任何行重複工作。

除此之外，還有不同的方法可以實現這一點，也有多種方法可以從中調整一些額外的效率。

讓我們從最低限度開始。由於提取 XML 可能是此操作中更昂貴的部分之一（如果不是最昂貴的部分），我們不希望必須這樣做兩次（因為我們有兩個操作要執行）。因此，我們創建一個臨時表並將 XML 中的數據提取到其中：

CREATE TABLE #TempImport
(
 Field1 DataType1,
 Field2 DataType2,
 ...
);

INSERT INTO #TempImport (Field1, Field2, ...)
 SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
        tab.col.value('XQueryForField2', 'DataType') AS [Field2],
        ...
 FROM   @XmlInputParam.nodes('XQuery') tab(col);

從那裡我們進行更新，然後插入：

UPDATE tab
SET    tab.Field1 = tmp.Field1,
      tab.Field2 = tmp.Field2,
      ...
FROM   [SchemaName].[TableName] tab
INNER JOIN #TempImport tmp
       ON tmp.IDField = tab.IDField
       ... -- more fields if PK or alternate key is composite

INSERT INTO [SchemaName].[TableName]
 (Field1, Field2, ...)
 SELECT tmp.Field1, tmp.Field2, ...
 FROM   #TempImport tmp
 WHERE  NOT EXISTS (
                      SELECT  *
                      FROM    [SchemaName].[TableName] tab
                      WHERE   tab.IDField = tmp.IDField
                      ... -- more fields if PK or alternate key is composite
                    );

現在我們已經完成了基本操作，我們可以做一些事情來優化：

1. 擷取插入到臨時表的@@ROWCOUNT 並與更新的@@ROWCOUNT 進行比較。如果它們相同，那麼我們可以跳過 INSERT
2. 擷取通過 OUTPUT 子句更新的 ID 值並從臨時表中刪除這些值。然後 INSERT 不需要WHERE NOT EXISTS(...)
3. 如果傳入數據中有任何不應同步的行*（*即既不插入也不更新），則應在執行 UPDATE 之前刪除這些記錄

CREATE TABLE #TempImport
(
 Field1 DataType1,
 Field2 DataType2,
 ...
);

DECLARE @ImportRows INT;
DECLARE @UpdatedIDs TABLE ([IDField] INT NOT NULL);

BEGIN TRY

 INSERT INTO #TempImport (Field1, Field2, ...)
   SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
          tab.col.value('XQueryForField2', 'DataType') AS [Field2],
          ...
   FROM   @XmlInputParam.nodes('XQuery') tab(col);

 SET @ImportRows = @@ROWCOUNT;

 IF (@ImportRows = 0)
 BEGIN
   RAISERROR('Seriously?', 16, 1); -- no rows to import
 END;

 -- optional: test to see if it helps or hurts
 -- ALTER TABLE #TempImport
 --   ADD CONSTRAINT [PK_#TempImport]
 --   PRIMARY KEY CLUSTERED (PKField ASC)
 --   WITH FILLFACTOR = 100;


 -- optional: remove any records that should not be synced
 DELETE tmp
 FROM   #TempImport tmp
 INNER JOIN [SchemaName].[TableName] tab
         ON tab.IDField = tmp.IDField
         ... -- more fields if PK or alternate key is composite
 WHERE  tmp.ModifiedDate < tab.ModifiedDate;

 BEGIN TRAN;

 UPDATE tab
 SET    tab.Field1 = tmp.Field1,
        tab.Field2 = tmp.Field2,
        ...
 OUTPUT INSERTED.IDField
 INTO   @UpdatedIDs ([IDField]) -- capture IDs that are updated
 FROM   [SchemaName].[TableName] tab
 INNER JOIN #TempImport tmp
         ON tmp.IDField = tab.IDField
         ... -- more fields if PK or alternate key is composite

 IF (@@ROWCOUNT < @ImportRows) -- if all rows were updates then skip, else insert remaining
 BEGIN
   -- get rid of rows that were updates, leaving only the ones to insert
   DELETE tmp
   FROM   #TempImport tmp
   INNER JOIN @UpdatedIDs del
           ON del.[IDField] = tmp.[IDField];

   -- OR, rather than the DELETE, maybe add a column to #TempImport for:
   -- [IsUpdate] BIT NOT NULL DEFAULT (0)
   -- Then UPDATE #TempImport SET [IsUpdate] = 1 JOIN @UpdatedIDs ON [IDField]
   -- Then, in below INSERT, add:  WHERE [IsUpdate] = 0

   INSERT INTO [SchemaName].[TableName]
     (Field1, Field2, ...)
     SELECT tmp.Field1, tmp.Field2, ...
     FROM   #TempImport tmp
 END;

 COMMIT TRAN;

END TRY
BEGIN CATCH
 IF (@@TRANCOUNT > 0)
 BEGIN
   ROLLBACK;
 END;

 -- THROW; -- if using SQL 2012 or newer, use this and remove the following 3 lines
 DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
 RAISERROR(@ErrorMessage, 16, 1);
 RETURN;
END CATCH;

我已經在 Imports / ETLs 上多次使用過這個模型，它們要麼有超過 1000 行，要麼可能有 500 行，總共有 20k 行 - 超過一百萬行。但是，我沒有測試臨時表中更新行的刪除與僅更新行之間的性能差異

$$ IsUpdate $$場地。

---

請注意關於使用 XML over TVP 的決定，因為一次最多導入 1000 行（在問題中提到）：

如果這里和那裡被呼叫了幾次，那麼很可能 TVP 中的微小性能提升可能不值得額外的維護成本（需要在更改使用者定義的表類型、應用程式碼更改等之前刪除 proc） . 但是，如果您要導入 400 萬行，一次發送 1000 行，那就是 4000 次執行（以及 400 萬行 XML 解析，無論它是如何分解的），即使只執行幾次，性能差異也會很小加起來有明顯的差異。

話雖如此，除了將 SELECT FROM @XmlInputParam 替換為 SELECT FROM @TVP 之外，我所描述的方法並沒有改變。由於 TVP 是只讀的，因此您無法從中刪除。我想你可以簡單地將 a 添加WHERE NOT EXISTS(SELECT * FROM @UpdateIDs ids WHERE ids.IDField = tmp.IDField)到最終的 SELECT （綁定到 INSERT）而不是簡單的WHERE IsUpdate = 0. 如果您以@UpdateIDs這種方式使用表變數，那麼您甚至可以不將傳入的行轉儲到臨時表中。

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