Using a subset of data as the target of a MERGE

Question

currently, we're merging against esqlProductTarget:

MERGE esqlProductTarget T
USING esqlProductSource S
ON (S.ProductID = T.ProductID)
WHEN MATCHED 
     THEN UPDATE
     SET    T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

rather than merging against the entire esqlProductTarget target dataset, can we merge against a subset like so:

MERGE (select * from esqlProductTarget where productid>1000) --the only change here is this line
USING esqlProductSource S
--etc

is it possible to merge a subset of records on the target?

Answer 1

You can certainly do this. You can use a CTE as the source or the target of MERGE.

WITH ePT AS
(
  SELECT
    *
  FROM 
    esqlProductTarget 
  WHERE productid > 1000
)
MERGE ePT AS T
USING esqlProductSource AS S
ON (S.ProductID = T.ProductID)
WHEN MATCHED 
     THEN UPDATE
     SET    T.Name = S.Name,
            T.ProductNumber = S.ProductNumber,
            T.Color = S.Color
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, Name, ProductNumber, Color)
     VALUES (S.ProductID, S.Name, S.ProductNumber, S.Color)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

Now, of course, you'll run into trouble if you try to MERGE in a productID that's less than 1000, but if you're confident in your data, SQL will let you do what you want to do.