Upsert on a range

Question

I'm aware of this:

IF EXISTS (SELECT * FROM table WHERE pk = @pk)
BEGIN
   --Update
END
ELSE
BEGIN 
   --INSERT
END

But this works for one row. Let's say I have a table min_max M and a temp table __MinMaxImport T. I want to INSERT from T to M, but UPDATE when the pair branch_no/product already exists in M.

INSERT:

INSERT INTO min_max
    (user_no, branch_no, product, min_stock, max_stock)
SELECT
    @user_no, _branch_no, _product, _min_stock, _max_stock
FROM 
    traxs_temp..__MinMaxImport 
WHERE 
    session_id = @session_id

UPDATE:

UPDATE min_max SET
    date_time = GETDATE(),
    user_no = @user_no,
    min_stock = _min_stock,
    max_stock = _max_stock
FROM
    min_max M
    INNER JOIN traxs_temp..__MinMaxImport T ON T._product = M.product 
AND T._branch_no = M.branch_no
WHERE 
    session_id = @session_id

What is the best solution to do this? I read about MERGE but I'm not sure how to use it.

Answer 1

MERGE should be able to do it.

MERGE INTO min_max
USING
(
    SELECT
        @user_no AS _user_no, _branch_no, _product, _min_stock, _max_stock
    FROM 
        traxs_temp..__MinMaxImport 
    WHERE 
        session_id = @session_id    
) AS Src
ON
    (min_max.branch_no = Src._branch_no) AND
    (min_max.product = Src._product)
WHEN MATCHED THEN 
UPDATE SET 
    date_time = GETDATE(),
    user_no = Src._user_no,
    min_stock = Src._min_stock,
    max_stock = Src._max_stock
WHEN NOT MATCHED BY TARGET THEN 
INSERT 
    (user_no, branch_no, product, min_stock, max_stock)
VALUES
    (Src._user_no,
    Src._branch_no,
    Src._product,
    Src._min_stock,
    Src._max_stock)
;