I have two tables, TableA & TableB, TableA is the production table and TableB is a working table. I need to Insert the rows in TableB that do not exist in TableA and update the rows in TableA that are in TableB, which I can do using the MERGE command. The problem I am running into is trying to Insert the row in TableB as a new row if it already exist in TableA. The MERGE command does not allow the insertion of a row on the WHEN MATCHED in the MERGE command. By updating the MATCHED row in TableA first before inserting the new row in TableB will keep the data unique.
I know I can do this by reading the table multiple times, once to do the UPDATE and then again to do the INSERT command. I would like to skip the double read by using the MERGE command if possible. If it cant be done then I will just run two pieces of code; one for UPDATE and one for INSERT