TSQL-Merge语句（新特点）
/**********************************************************
MERGE，这种语法可以融合UPDATE、DELETE和INSERT。
特别适合于将交易型的记录集合并到快照性的结果集中去。
非常具有代表性的应用场景就是库存管理，库存管理应用(俗称进销存)中
经常需要获得某个时间点上的库存，也称为库存结余。
**********************************************************/
--创建测试表
USE Blog
GO
--创建库存快照表
--Inventory-存货清单
CREATE TABLE Inventory_Snapshot
(
ProductID int PRIMARY KEY NOT NULL,--产品
Quantity int NOT NULL --数量
)
GO
--创建库存操作表
CREATE TABLE Inventory_Operation
(
OperationID int PRIMARY KEY IDENTITY(1,1), --操作ID
OperationDate datetime, --操作时间
OperationType int, --1:入仓; 2:出仓;
ProductID int, --产品
Quantity int --数量
)
GO
--创建存储过程用于新的库存操作
CREATE PROCEDURE sp_Inventory_Operation
@productID int,--产品ID
@operationDate datetime,--操作时间
@operationType int, --1:入仓; 2:出仓;
@quantity int --数量
AS
INSERT INTO Inventory_Operation
(OperationDate, OperationType, ProductID, Quantity)
VALUES
(@operationDate, @operationType, @productID, @quantity)
GO
--创建存储过程用于库存快照计算
CREATE PROCEDURE sp_Inventory_Snapshot_Process
@processDate datetime --时间
AS
MERGE Inventory_Snapshot AS invs --为快照表取别名
USING
(
SELECT ProductID, Sum(ABSQuantity) AS SubTotal
FROM (
SELECT ProductID, Quantity *
CASE OperationType --1:入仓; 2:出仓;
WHEN 1 THEN 1
WHEN 2 THEN -1
ELSE 0
END AS ABSQuantity --如果是入库，则是正数，如果是出库，则是负数
FROM Inventory_Operation
WHERE OperationDate = @processDate
) AggInvo
GROUP BY AggInvo.ProductID
) AS invo(ProductID, SubTotal) --库存操作表
ON (invs.ProductID = invo.ProductID) --将库存表与快照关联起来
WHEN MATCHED --更新
AND invs.Quantity <> invo.SubTotal
AND invs.Quantity <> invo.SubTotal * -1
THEN
UPDATE SET invs.Quantity = invs.Quantity + invo.SubTotal
WHEN MATCHED --删除
AND invs.Quantity = invo.SubTotal * -1
THEN DELETE
WHEN NOT MATCHED BY TARGET --新增
THEN INSERT VALUES (invo.ProductID, invo.SubTotal);
GO
--清空库存快照表
DELETE FROM dbo.Inventory_Snapshot
--测试应用程序逻辑,先入库操作,填充库存
EXEC sp_Inventory_Operation 1000, '2008-9-7', 1, 500
EXEC sp_Inventory_Operation 1001, '2008-9-7', 1, 300
EXEC sp_Inventory_Operation 1002, '2008-9-7', 1, 250
--测试当前的库存快照
EXEC sp_Inventory_Snapshot_Process '2008-9-7'
SELECT * FROM Inventory_Snapshot
GO
--第二次测试,1001 产品出库 200,新入库1003产品数据300; 1000产品出库200
EXEC sp_Inventory_Operation 1001, '2008-9-8', 2, 200
EXEC sp_Inventory_Operation 1003, '2008-9-8', 1, 300
EXEC sp_Inventory_Operation 1000, '2008-9-8', 2, 200
--查看当前库存
EXEC sp_Inventory_Snapshot_Process '2008-9-8'
SELECT * FROM Inventory_Snapshot
GO
--第三次测试,全部出库操作,
EXEC sp_Inventory_Operation 1000, '2008-9-9', 2, 200
EXEC sp_Inventory_Operation 1002, '2008-9-9', 2, 250
EXEC sp_Inventory_Operation 1003, '2008-9-9', 2, 300
--查看当前库存
EXEC sp_Inventory_Snapshot_Process '2008-9-9'
SELECT * FROM Inventory_Snapshot
/**********************************************************
总结
**********************************************************/
在 SQL Server 2008 中，您可以使用 MERGE 语句在一条语句中执行插入、更新或删除操作。MERGE 语句允许您将数据源与目标表或视图联接，然后根据该联接的结果对目标对象执行多项操作。例如，您可以使用 MERGE 语句执行以下操作：
· 使用一个语句有条件地在单个目标表中插入或更新行。
如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。
· 同步两个表。
根据与源数据的差别在目标表中插入、更新或删除行。
MERGE 语法包括五个主要子句：
· MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图。
· USING 子句用于指定要与目标联接的数据源。
· ON 子句用于指定决定目标与源的匹配位置的联接条件。
· WHEN 子句用于根据 ON 子句的结果指定要执行的操作。
· OUTPUT 子句针对更新、插入或删除的目标对象中的每一行返回一行。
