SqlServer_MERGE命令;

说明：通过MERGE命令，可以将多个DML动作语句(INSERT、UPDATE、DELETE)组合成一个整体动作，从而提高性能(它们可共享许多相同的物理操作)和简化事务。
(1)、示例
USE AdventureWorks2008
GO
CREATE TABLE Sales.MonthlyRollup
{
	Year smallint NOT NULL,Month tinyint NOT NULL,ProductID int NOT NULL FOREIGN KEY REFERENCES Production.Product(ProductID),QtySold int NOT NULL,CONSTRAINT PKYearMonthProductID PRIMARY KEY (Year,Month,ProductID)
}
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 >= '2003-08-01' AND soh.OrderDate < '2003-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,ProductID,QtySold)
	VALUES (DATEPART(YY,s.ORDERDATE),DATEPART(M,s.OrderDate),s.ProductID,s.QtySold);
切记，MERGE语句最后的分号是必须的；
(2)、语法
MERGE [TOP (<expression>) [PERCENT]]
	[INTO] <target table> [WITH (<hint>)] [[AS] <alias>]
USING <source query>
ON <condition for join with target>
[WHEN MATCHED [AND <clause search condition>]
	THEN <merge matched>]
[WHEN NOT MATCHED [BY TARGET] [AND <clause search condition>] 
	THEN <merge not matched>]
[WHEN NOT MATCHED BY SOURCE [AND <clause search condition>] 
	THEN <merge matched>]
[<output clause>]
[OPTION (<query hint> [,...n])];

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!