我正在从此页面学习如何使用SQL Server MERGE语句:https : //technet.microsoft.com/zh- cn/library/bb522522(v= sql.105).aspx
MERGE dbo.FactBuyingHabits AS Target USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID) WHEN MATCHED THEN UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate WHEN NOT MATCHED BY TARGET THEN INSERT (CustomerID, ProductID, LastPurchaseDate) VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate) OUTPUT $action, Inserted.*, Deleted.*;
但是,我可以找到的所有示例(例如上面的示例)都使用一个实际的表作为Source。是否可以直接传递数据?我宁愿不为此创建一个临时表(如果可能并建议使用?)如何修改上面的查询?
谢谢
试试这种格式:
MERGE TARGET_TABLE AS I USING (VALUES ('VALUE1','VALUE2')) as s(COL1,COL2) ON I.COL1 = s.COL1 WHEN MATCHED THEN