[SQL] Merge 應用

此範例同 [SQL] OUTPUT 語法，差別在於 OUPUT 與法是假如 StockQty 內產品一定存在，而 Merge 則是不一定存在，且庫存量為 0 時，必須把產品刪除，藉此練習 OUTPUT 和 Merge 語法

下列在 AdventureWorks 中利用產品（Production）、庫存量（StockQty）和入領料（Stock、StockDetail）來說明 Merge 語法

• 建立基礎 Table 並插入 Sample Data

IF OBJECT_ID('StockQty') IS NOT NULL
  DROP TABLE StockQty
 
IF OBJECT_ID('Stock') IS NOT NULL
  DROP TABLE Stock

IF OBJECT_ID('StockDetail') IS NOT NULL
  DROP TABLE StockDetail
 
IF OBJECT_ID('Production') IS NOT NULL
  DROP TABLE Production

CREATE TABLE Production (ProdID int Primary key, ProdName nchar(50))
INSERT INTO Production VALUES(1,N'SQL Server 2014')
INSERT INTO Production VALUES(2,N'SQL Server 2012')
INSERT INTO Production VALUES(3,N'SQL Server 2008 R2')
INSERT INTO Production VALUES(4,N'SQL Server 2008')
INSERT INTO Production VALUES(5,N'SQL Server 2005')

-- StockQty 沒有任何資料，當 StockDetail 有資料時，
-- 會透過 Merge 語法來 insert、update 和 delete StockQty Table 內資料
CREATE TABLE StockQty (ProdID int Primary key, Qty int)

CREATE TABLE Stock (SID int Primary key, [Date] date)
CREATE TABLE StockDetail (SID int Primary key(SID,ProdID), ProdID int , Qty int)
GO

• 建立 View：方便後續觀察執行結果

IF OBJECT_ID('vwStockQty') IS NOT NULL
  DROP VIEW vwStockQty

IF OBJECT_ID('vwStock') IS NOT NULL
  DROP VIEW vwStock
GO
 
CREATE VIEW vwStockQty
AS
  SELECT 
    P.ProdID ,
    P.ProdName ,
    Q.Qty
  FROM StockQty AS Q 
    JOIN Production AS P ON Q.ProdID = P.ProdID
GO

CREATE VIEW vwStock
AS
  SELECT 
    S.SID,
    S.[Date],
    SD.ProdID,
    P.ProdName,
    SD.Qty
  FROM Stock AS S
    JOIN StockDetail AS SD ON S.SID = SD.SID
    JOIN Production AS P ON SD.ProdID = P.ProdID
GO

• 透過 Store Procedure 來執行 DML

重點在於 dmlStockDetailI、dmlStockDetailD 和 dmlStockDetailU 中的 Merge 應用

IF OBJECT_ID('dmlStockI') IS NOT NULL
  DROP PROCEDURE dmlStockI

IF OBJECT_ID('dmlStockD') IS NOT NULL
  DROP PROCEDURE dmlStockD
  
IF OBJECT_ID('dmlStockDetailI') IS NOT NULL
  DROP PROCEDURE dmlStockDetailI

IF OBJECT_ID('dmlStockDetailD') IS NOT NULL
  DROP PROCEDURE dmlStockDetailD

IF OBJECT_ID('dmlStockDetailU') IS NOT NULL
  DROP PROCEDURE dmlStockDetailU
GO

CREATE PROCEDURE dmlStockI
(
  @SID int
)
AS
  BEGIN
   
    INSERT INTO Stock (SID , [Date])
      VALUES(@SID,CAST(getdate() AS date))
 
  END
GO

CREATE PROCEDURE dmlStockD (@SID int)
AS
  BEGIN
    DELETE FROM Stock WHERE SID = @SID
  END
GO

CREATE PROCEDURE dmlStockDetailI
(
  @SID int ,
  @ProdID int ,
  @Qty int
)
AS
  BEGIN
    
    INSERT INTO StockDetail (SID,ProdID,Qty)
      VALUES(@SID,@ProdID,@Qty)
 
    MERGE StockQty AS T
    USING (SELECT @ProdID,@Qty) AS S (ProdID,Qty)
      ON (T.ProdID = S.ProdID)
    WHEN MATCHED THEN
      UPDATE SET T.Qty = T.Qty + S.Qty
    WHEN NOT MATCHED THEN
      INSERT (ProdID,Qty) 
        VALUES (S.ProdID,S.Qty);

  END
GO

CREATE PROCEDURE dmlStockDetailD (@SID int)
AS
  BEGIN
  
    CREATE TABLE #Temp (ProdID int Primary Key, Qty int)

    DELETE StockDetail
    OUTPUT deleted.ProdID , deleted.Qty into #Temp
    WHERE SID = @SID

    MERGE StockQty AS T
    USING #Temp AS S
      ON (T.ProdID = S.ProdID)
    WHEN MATCHED AND T.Qty - S.Qty <= 0 THEN 
      DELETE
    WHEN MATCHED THEN
      UPDATE SET T.Qty = T.Qty - S.Qty
    WHEN NOT MATCHED THEN
      INSERT (ProdID,Qty) 
        VALUES (S.ProdID,S.Qty);
  
    IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
      DROP TABLE #Temp

  END
GO

CREATE PROCEDURE dmlStockDetailU
(
  @SID int,
  @ProdID int,
  @Qty int
)
AS
  BEGIN

    CREATE TABLE #Temp(ProdID int Primary Key, Qty int)

    UPDATE StockDetail
    SET Qty = @Qty
    OUTPUT deleted.ProdID , inserted.Qty - deleted.Qty into #Temp
    WHERE SID = @SID 
      AND ProdID = @ProdID 

    MERGE StockQty AS T
    USING #Temp AS S
      ON (T.ProdID = S.ProdID)
    WHEN MATCHED AND T.Qty + S.Qty <= 0 THEN
      DELETE
    WHEN MATCHED THEN
      UPDATE SET T.Qty = T.Qty + S.Qty
    WHEN NOT MATCHED THEN
      INSERT (ProdID,Qty) 
        VALUES (S.ProdID,S.Qty);

    IF OBJECT_ID('TempDB..#Temp') IS NOT NULL
      DROP TABLE #Temp

  END
GO

• 執行 1：第一筆入料單

EXEC dmlStockI @SID = 1
EXEC dmlStockDetailI @SID = 1 , @ProdID = 5 , @Qty = 10

• 執行 2：第二筆出料單

EXEC dmlStockI @SID = 2
EXEC dmlStockDetailI @SID = 2 , @ProdID = 5 , @Qty = -7

• 執行 3：修正第二筆出料單數量

EXEC dmlStockDetailU @SID = 2 , @ProdID = 5 , @Qty = -5

• 執行 4：刪除第二筆出料單

EXEC dmlStockd @SID = 2
EXEC dmlStockDetaild @SID = 2

• 執行 5：刪除第一筆入料單

EXEC dmlStockDetailD @SID = 1
EXEC dmlStockD @SID = 1

延伸閱讀
• [SQL] OUTPUT 語法

參考資料
• 使用Merge語法來同步資料時小心誤砍了不該刪的資料
• SQL Server Merge condition on declare value problem
  
• use MERGE in the right way