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?

i think you need to include Circle 'productid>1000' in the Joining caluse , like - ON (S.ProductID = T.ProductID) AND T.ProductId>1000 OR in where clause like - WHEN MATCHED AND T.ProductId >1000 OR WHERE NOT MATCHED AND T.ProductId >1000 — Sahi, Apr 11 '18 at 09:26

score 5 · Answer 1 · answered Apr 12 '18 at 02:38

5

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.

answered Apr 12 '18 at 02:38

Eric Brandt

7,886
3
18
35

in that case, is it possible to make the USING statement a subset of esqlProductSource? can we use ```USING``` with a cte? – Alex Gordon Apr 12 '18 at 02:42
The USING clause of the MERGE statement is just your source data, and yes, you can use a CTE there, too. But to be honest, if you're using CTEs for both the source and the target, your code will be easier to read, easier to debug, and will perform faster if you don't use MERGE. Create your data sets, then do your CRUD operations, unless there's some (very) compelling reason to do it all with one statement. – Eric Brandt Apr 12 '18 at 02:47
in that case, what is the purpose of merge? – Alex Gordon Apr 12 '18 at 02:58
Don't get me wrong. MERGE does exactly what you want it to do. But ever since it was introduced there's been a LOT of discussion about whether the single statement, as the query optimizer uses it, is the best way to do what the statement does. The only thing that's clear (in my opinion) is that separate statements perform as well or better than MERGE, There's a Holy War over readability (as well as with CTEs), and so I use MERGE less often than I could. Google 'tsql merge performance' and see for yourself. Plenty to read there. – Eric Brandt Apr 12 '18 at 03:04

score 0 · Answer 2 · answered Apr 10 '18 at 18:12

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.

Using a subset of data as the target of a MERGE

2 Answers2