CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Alex Gordon
Alex Gordon

Reputation: 60711

Using a subset of data as the target of a MERGE

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?

Upvotes: 4

Views: 4530

Answers (2)

Eric Brandt
Eric Brandt

Reputation: 8101

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.

Upvotes: 7

Anthony Hancock
Anthony Hancock

Reputation: 931

According to the documentation (https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql)

target_table Is the table or view against which the data rows from are matched based on . target_table is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement.

So if you don't want to merge against the table, you would have to create a view to represent your subset. But that is probably not going to provide any sort of performance benefit, so you might as well just stick with merging against the table or breaking into separate update and insert statements if your concern is performance.

Upvotes: 0

Related Questions