Rewrite Merge statement to Insert and update separate queries

Question

I want to rewrite the below merge query to separate insert and update statements. Any suggestions.

MERGE INTO TestTable T
USING (TestTable2 S) AS S ON T.UserID = S.UserID
WHEN MATCHED THEN UPDATE SET State = S.State
WHEN NOT MATCHED THEN INSERT (userID, State)
VALUES (S.UserID, S.State)
;

Answer 1

Hi @kccrga ,

Welcome to Microsoft Q&A!

Please refer below example:

drop table if exists TestTable,TestTable2  
  
create table TestTable  
(UserID int,  
State varchar(10))   
  
insert into TestTable values  
(1,'a')  
  
create table TestTable2  
(UserID int,  
State varchar(10))   
  
insert into TestTable2 values  
(1,'aa'),  
(2,'b')  

UPDATE T  
SET State=S.State  
FROM TestTable T INNER JOIN TestTable2 S ON T.userID = S.userID  
  
INSERT INTO TestTable  
SELECT   s.UserID,s.State     
FROM TestTable2 S  
WHERE NOT EXISTS (  
        SELECT *  
        FROM TestTable T  
        WHERE T.userID = S.userID)  
  
select * from TestTable  

Output:

UserID State  
1 aa  
2 b  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.