SQLServer MERGE 用法

今天寫儲存過程的時候，遇到了從一個大json串中取一個id號比對，如果是存在的就更新，不存在就新增。查了資料，覺得還是用merge比較好，這個用法坑也不少，一起來看看程式碼吧。

MERGE tABatch AS t  -- 需要操作的主體表
USING(
	SELECT batchNo,COUNT(0) AS waitTotal
	FROM (
	SELECT batchNo FROM OPENJSON(@checkJson)
	WITH(batchNo VARCHAR(50) '$.batchNo')
    ) t GROUP BY t.batchNo
	) AS s (batchNo,waitTotal) -- USING裡面是資料來源，as以後，必須按照順序指定欄位名
    ON t.batchNo = s.batchNo  -- 這裡是對比的條件
	WHEN MATCHED THEN 
	-- 如果有匹配就更新,只用寫要更新的內容即可
		UPDATE SET t.waitTotal = t.waitTotal+s.waitTotal
	WHEN NOT MATCHED THEN 
	-- 沒有匹配就新增,注意這裡的INSERT不能再跟表名了
	INSERT  
	(
		batchNo,
		waitTotal,
		createTime
	)VALUES(
		s.batchNo,
		s.waitTotal,
		getdate()
	);
-- merge必須分號結尾,不然報錯

上面是我的示例，如果不放心可以來看看官方的：

CREATE PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS   
BEGIN  
    SET NOCOUNT ON;  
-- Update the row if it exists.      
    UPDATE Production.UnitMeasure  
SET Name = @Name  
WHERE UnitMeasureCode = @UnitMeasureCode  
-- Insert the row if the UPDATE statement failed.  
IF (@@ROWCOUNT = 0 )  
BEGIN  
    INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)  
    VALUES (@UnitMeasureCode, @Name)  
END  
END;  
GO  
-- Test the procedure and return the results.  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';  
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure  
WHERE UnitMeasureCode = 'ABC';  
GO  
  
-- Rewrite the procedure to perform the same operations using the 
-- MERGE statement.  
-- Create a temporary table to hold the updated or inserted values 
-- from the OUTPUT clause.  
CREATE TABLE #MyTempTable  
    (ExistingCode nchar(3),  
     ExistingName nvarchar(50),  
     ExistingDate datetime,  
     ActionTaken nvarchar(10),  
     NewCode nchar(3),  
     NewName nvarchar(50),  
     NewDate datetime  
    );  
GO  
ALTER PROCEDURE dbo.InsertUnitMeasure  
    @UnitMeasureCode nchar(3),  
    @Name nvarchar(25)  
AS   
BEGIN  
    SET NOCOUNT ON;  
   -- 重點看這一段
    MERGE Production.UnitMeasure AS target  
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)  
    ON (target.UnitMeasureCode = source.UnitMeasureCode)  
    WHEN MATCHED THEN   
        UPDATE SET Name = source.Name  
WHEN NOT MATCHED THEN  
    INSERT (UnitMeasureCode, Name)  
    VALUES (source.UnitMeasureCode, source.Name)  
    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;  
END;  
GO  
-- Test the procedure and return the results.  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';  
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';  
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';  
  
SELECT * FROM #MyTempTable;  
-- Cleanup   
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');  
DROP TABLE #MyTempTable;  
GO