推荐订阅源

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 子句针对更新、插入或删除的目标对象中的每一行返回一行。