没有源表的 SQL Server MERGE

Question

我正在学习如何使用 SQL Server MERGE 语句从此页面: https://technet.microsoft.com/en-us/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.*;

但是我能找到的所有示例(例如上面的示例)都使用实际的表作为源。可以直接传数据吗？我宁愿不为此创建临时表(如果可能并推荐的话？)如何修改上面的查询？

谢谢

Answer 1

尝试这种格式:

MERGE TARGET_TABLE AS I
USING (VALUES ('VALUE1','VALUE2')) as s(COL1,COL2)
ON I.COL1 = s.COL1
WHEN MATCHED THEN

你也可以引用这个: "Merge" style operation with literal values?