SQL Server MERGE without source table

Question

I am learning to use the SQL Server MERGE statement on 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 (like the one above) use the actual table as the source. Is it possible to transfer data directly? I would prefer not to create a temporary table for this (if it is possible and recommended?). How would the request change above?

thank

Answer 1

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 can also refer to this: "Combine" style with literal values?

Answer 2

:

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.*;

Answer 3

I needed an even simpler version, and I 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();