SQL Merge funcation

Question

I need to merge 2 tables with insert data in both cases (matched or not ) but in mathed I add come chars to key> example

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.MatchingField1 = MyTable.MatchingField1
WHEN MATCHED THEN
INSERT VALUES(MyTempTable.MatchingField1+'A001', MyTempTable.UpdateField1)
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.MatchingField1, MyTempTable.UpdateField1)

I got error message "Insert not allowed after matched"

Answer 1

Try another statement:

insert MyTable  
select coalesce( t1.MatchingField1 + 'A001', t2.MatchingField1), t2.UpdateField1  
from MyTempTable t2  
left join MyTable t1 on t1.MatchingField1 = t2.MatchingField1  

Answer 2

"Insert not allowed after matched"

Matched means, the data exists in both tables, so what do you want to INSERT here; you would get duplicated data?

Answer 3

SQL Merge does not allow you insert new records after "WHEN MATCHED". That doesn't make any sense.

What exactly are you trying to do? It looks like you are actually trying to insert some kind of sequence if the record exists. Merge is not capable of doing that.

See:
https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver16

Answer 4

Hi @Dr Hany Maher
As experts answered above, it is not allowed to 'INSERT' on the condition of 'WHEN MATCHED'.
Basically, we do UPDATE operation after WHEN MATCHED.
In this issue, you might need this:

WHEN MATCHED THEN  
  UPDATE SET MyTable.MatchingField1 = MyTempTable.MatchingField1+'A001'  
            ,MyTable.Need_to_UpdateField1 = MyTempTable.UpdateField1  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.