SQL Server 2005 中的 MERGE 命令

我一直在嘗試在 SQL Server 2005 中開發 MERGE。它還沒有所有功能，因為我一直在努力解決“如何將合併邏輯作為一個集合而不是行工作”。

到目前為止，我所擁有的基本上與 MERGE 無關 - 只是“如果記錄已經存在，請刪除”，然後進行完整插入（大約 8,000 行）。

但它確實有效——在佈景上工作。

這是我的桌子 - ItemStockFake：

我聲明來源：

SET NOCOUNT ON


declare @ItemStockFake table(
   [ItemNo] [varchar](10) NOT NULL primary key clustered,
   [Tier1] [varchar](10) NULL,
   [Tier2] [varchar](10) NULL,
   [QtyOnOrder] [int] NOT NULL,
   [QtyOnHand] [int] NOT NULL,
   [LocalQtyOnHand] [int] NOT NULL,
   [ItemCancelled] [bit] NULL,
   [DueDate] [smalldatetime] NULL,
   [StartDate] [smalldatetime] NOT NULL,
   [ExpiryDate] [smalldatetime] NOT NULL,
   [BestDeliveryOptionId] [int] NOT NULL
)

寫入源（只有幾條記錄 - 現實生活中有數千條）：

INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623685','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623693','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623701','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623719','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')
INSERT INTO @ITEMSTOCKFAKE([ItemNo],[Tier1],[LocalQtyOnHand],[QtyOnOrder],[QtyOnHand],[ItemCancelled],[DueDate],[StartDate],[ExpiryDate],BestDeliveryOptionId)    VALUES ('35623727','WA711',0,1000,0,0,'20160502 06:00:00','2015-10-14 14:10:45','20160502 06:00:00','100')

然後做“魔術” - 基本上從數據庫中刪除記錄，然後再插入它們：

BEGIN TRY

           --=====================================================================================
           -- A T T E N T I O N - DELETING THE RECORDS CURRENT IN THE DATABASE
           -- to make room for the new inserts
           --=====================================================================================

           BEGIN TRANSACTION T1


           DELETE B
           FROM @ItemStockFake A
           INNER JOIN ItemStockFake B ON A.ITEMNO = B.ITEMNO

           PRINT CAST( @@ROWCOUNT  AS VARCHAR) + ' number of records deleted.'

           INSERT INTO [dbo].[ItemStockFake]
           SELECT * FROM @ItemStockFake

           PRINT CAST( @@ROWCOUNT  AS VARCHAR) + ' number of records inserted.'

           COMMIT TRANSACTION T1

END TRY

BEGIN CATCH

       WHILE @@TRANCOUNT > 0
             ROLLBACK TRANSACTION



       PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
             'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

       PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
             'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

       PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
             'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

       PRINT 'ERROR MESSAGE: '
       PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH

沒什麼大不了。

我想實現的是這樣的：

...
BEGIN TRY
  INSERT table1
END TRY
BEGIN CATCH
  IF ERROR_NUMBER = 2627
      UPDATE table1
  ELSE
      -- Process the real error
END CATCH
...

這來自這個問題here。

不過，我想成組工作，並儘快完成整個操作。

我想我的問題是：為什麼不先更新存在的記錄，然後插入所有不存在的記錄？通過強迫自己陷入 PK 違規錯誤，你得到了什麼？

UPDATE B
SET Tier1 = A.Tier1,
   Tier2 = A.Tier2,
   QtyOnOrder = A.QtyOnOrder,
   QtyOnHand = A.QtyOnHand,
   LocalQtyOnHand = A.LocalQtyOnHand,
   ItemCancelled = A.ItemCancelled,
   DueDate = A.DueDate,
   StartDate = A.StartDate,
   ExpiryDate = A.ExpiryDate,
   BestDeliveryOptionId = A.BestDeliveryOptionId
FROM @ItemStockFake A
INNER JOIN ItemStockFake B ON A.ITEMNO = B.ITEMNO;

INSERT INTO ItemStockFake (Tier1, Tier2, QtyOnOrder, QtyOnHand, LocalQtyOnHand, ItemCancelled, DueDate, StartDate, ExpiryDate, BestDeliveryOptionId)
SELECT Tier1, Tier2, QtyOnOrder, QtyOnHand, LocalQtyOnHand, ItemCancelled, DueDate, StartDate, ExpiryDate, BestDeliveryOptionId
FROM @ItemStockFake A
WHERE NOT EXISTS (
       SELECT 1 
       FROM ItemStockFake B
       WHERE B.ITEMNO = A.ITEMNO
   );

你可以這樣做：

BEGIN TRAN

UPDATE     A
SET          [Tier1]                  = B.[Tier1]               
          , [Tier2]                  = B.[Tier2] 
          , [QtyOnOrder]             = B.[QtyOnOrder] 
          , [QtyOnHand]              = B.[QtyOnHand] 
          , [LocalQtyOnHand]         = B.[LocalQtyOnHand] 
          , [ItemCancelled]          = B.[ItemCancelled] 
          , [DueDate]                = B.[DueDate] 
          , [StartDate]              = B.[StartDate] 
          , [ExpiryDate]             = B.[ExpiryDate] 
          , [BestDeliveryOptionId]   = B.[BestDeliveryOptionId]
FROM       [dbo].[ItemStockFake] AS A
INNER JOIN @ItemStockFake        AS B
   ON     A.ITEMNO = B.ITEMNO

INSERT INTO [dbo].[ItemStockFake] (Tier1, Tier2, QtyOnOrder, QtyOnHand, LocalQtyOnHand, ItemCancelled, DueDate, StartDate, ExpiryDate, BestDeliveryOptionId)
SELECT     A.Tier1, A.Tier2, A.QtyOnOrder, A.QtyOnHand, A.LocalQtyOnHand, A.ItemCancelled, A.DueDate, A.StartDate, A.ExpiryDate, A.BestDeliveryOptionId
FROM       @ItemStockFake        AS A
LEFT JOIN  [dbo].[ItemStockFake] AS B
   ON     A.ITEMNO = B.ITEMNO
WHERE      B.ITEMNO IS NULL

COMMIT

第一個查詢獲取 ItemStockFake 中存在的所有行並更新它們。第二個查詢在 ItemStockFake 中插入所有不存在的行。

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