实际使用MERGE命令:
插入第一天的销售情况:
MERGE Sales.MonthlyRollup AS smr
USING
(
SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2007-08-01' AND soh.OrderDate < '2007-08-02'
GROUP BY soh.OrderDate, sod.ProductID
) AS s
ON (s.ProductID = smr.ProductID)
WHEN MATCHED THEN
UPDATE SET smr.QtySold = smr.QtySold + s.QtySold
WHEN NOT MATCHED THEN
INSERT (Year, Month, ProductID, QtySold)
VALUES (DATEPART(yy, s.OrderDate),
DATEPART(m, s.OrderDate),
s.ProductID,
s.QtySold);
第二天的结果更新:
MERGE Sales.MonthlyRollup AS smr
USING
(
SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2007-08-02' AND soh.OrderDate < '2007-08-03'
GROUP BY soh.OrderDate, sod.ProductID
) AS s
ON (s.ProductID = smr.ProductID)
WHEN MATCHED THEN
UPDATE SET smr.QtySold = smr.QtySold + s.QtySold
WHEN NOT MATCHED THEN
INSERT (Year, Month, ProductID, QtySold)
VALUES (DATEPART(yy, s.OrderDate),
DATEPART(m, s.OrderDate),
s.ProductID,
s.QtySold);
BY TARGE和BY SOURCE:
NOT MATCHED [BY TARGET]:这通常对应根据源表中的数据将行插入到表中的场景。
MATCHED [BY TARGET]: 这暗示了行已经存在于目标表上,因此很有可能对目标表的行执行更新操作。
NOT MATCHED BY SOURCE:这通常用于处理源表中缺少的行,在这种场景下,通常将删除目标表中的行。
使用OUTPUT子句手机受影响的行:
$action:只用于MERGE。返回INSERTED、UPDATED或DELETED,表明对特定的行执行的操作。
inserted:用于MERGE、INSERT或UPDATE。对内部工作表的引用,该工作表包含了为给定行插入的数据的引用。注意,这包括了已更新数据的当前值。
deleted: 用于MERGE、DELETE或UPDATE。对内部工作表的引用,该工作表包含了从给定行中删除的数据的引用。注意,这包括来了已更新数据之前的值。
截断MonthlyRollup表,清空表中的所有数据:
TRUNCATE TABLE Sales.MonthlyRollup;
第一次执行执行MERGE语句包括OUTPUT子句:
</pre><pre class="sql" name="code">MERGE Sales.MonthlyRollup AS smr
USING
(
SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2007-08-01' AND soh.OrderDate < '2007-08-02'
GROUP BY soh.OrderDate, sod.ProductID
) AS s
ON (s.ProductID = smr.ProductID)
WHEN MATCHED THEN
UPDATE SET smr.QtySold = smr.QtySold + s.QtySold
WHEN NOT MATCHED THEN
INSERT (Year, Month, ProductID, QtySold)
VALUES (DATEPART(yy, s.OrderDate),
DATEPART(m, s.OrderDate),
s.ProductID,
s.QtySold)
OUTPUT $action,
inserted.Year,
inserted.Month,
inserted.ProductID,
inserted.QtySold,
deleted.Year,
deleted.Month,
deleted.ProductID,
deleted.QtySold;
第二次执行:
MERGE Sales.MonthlyRollup AS smr
USING
(
SELECT soh.OrderDate, sod.ProductID, SUM(sod.OrderQty) AS QtySold
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.OrderDate >= '2007-08-02' AND soh.OrderDate < '2007-08-03'
GROUP BY soh.OrderDate, sod.ProductID
) AS s
ON (s.ProductID = smr.ProductID)
WHEN MATCHED THEN
UPDATE SET smr.QtySold = smr.QtySold + s.QtySold
WHEN NOT MATCHED THEN
INSERT (Year, Month, ProductID, QtySold)
VALUES (DATEPART(yy, s.OrderDate),
DATEPART(m, s.OrderDate),
s.ProductID,
s.QtySold)
OUTPUT $action,
inserted.Year,
inserted.Month,
inserted.ProductID,
inserted.QtySold,
deleted.Year,
deleted.Month,
deleted.ProductID,
deleted.QtySold;