使用MERGE命令同步数据

曾经终败给现在 2022-09-23 15:58 76阅读 0赞

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