使用MERGE命令同步数据

来源：互联网发布：foobar2000音质优化版编辑：程序博客网时间：2024/05/22 14:25

实际使用MERGE命令：

插入第一天的销售情况：

MERGE Sales.MonthlyRollup AS smrUSING(  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 sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN  UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldWHEN 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 smrUSING(  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 sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN  UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldWHEN 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 smrUSING(  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 sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN  UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldWHEN 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 smrUSING(  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 sON (s.ProductID = smr.ProductID)WHEN MATCHED THEN  UPDATE SET smr.QtySold = smr.QtySold + s.QtySoldWHEN 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;

0 0