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.*;
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?
Thank you
Try this format:
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?
You could do it like this:
Declare @customerID int = 1;
Declare @productID int = 1;
Declare @purchaseDate date = '1900-01-01';
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID = @customerID,
ProductID = @productID,
PurchaseDate = @purchaseDate) 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.*;
I needed an even simpler version and came to this solution:
MERGE Target_Table
USING (VALUES (0)) as s(x)
ON last_run is not null
WHEN not matched then
insert (last_run) values(getdate())
when matched then
update set last_run=getDate();