使用MERGE命令同步数据

实际使用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;