今天写存储过程的时候,遇到了从一个大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