How can I use insert with select from other table in a merge statement?

Question

I have this query.

MERGE sales.category t 
    USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
    THEN UPDATE SET 
        t.category_name = s.category_name,
        t.amount = s.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT t(category_id, category_name, amount)
         VALUES select s.category_id, s.category_name, s.amount from s
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

How can I use insert with select from other table in a merge statement?

Answer 1

You wouldn't. You have already defined S so you can reference those columns in the VALUES clause. You also don't put the alias of the destination table prior to the destination columns:

    THEN INSERT (category_id, category_name, amount)
         VALUES (s.category_id, s.category_name, s.amount)

Answer 2

This might be helpful for your requirement to use the other table.

You can use the join as a Source and then you can use it for Insert as well.