没有源表的SQL Server MERGE [英] SQL Server MERGE without a source table
问题描述
我正在从此页面学习如何使用SQL Server MERGE语句: https://technet.microsoft.com/zh-我们/library/bb522522(v=sql.105).aspx
I am learning how to use SQL Server MERGE statement from this page: 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.*;
但是,我可以找到的所有示例(例如上面的示例)都使用一个实际的表作为Source.是否可以直接传递数据?我宁愿不为此创建一个临时表(如果可能并建议?) 上面的查询将如何修改?
However all the examples I can find (such as the one above) are using an actual table as Source. Is it possible to directly pass the data? I would rather not create a temporary table for that (if possible and recommended?) How would the query above be modified?
谢谢
推荐答案
尝试以下格式:
MERGE TARGET_TABLE AS I
USING (VALUES ('VALUE1','VALUE2')) as s(COL1,COL2)
ON I.COL1 = s.COL1
WHEN MATCHED THEN
您也可以参考以下内容: 合并";带有文字值的样式操作?
You could also reference this: "Merge" style operation with literal values?
这篇关于没有源表的SQL Server MERGE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!