Multiple Merge function in single SP

Question

Hi Team,

I wanted to compare 2 tables and update the second table with 3 constant values say A, B,C.
I can hardcode the 3 values in my Insert/Update query.

Merge function is Apt for my solution and working fine but I want to use the merge function 3 times in a single stored procedure for updating A,B,C.

But only the first merge is executing and the 2nd and 3rd merge statements are not executing.

BEGIN
Merge 1 - Update A;
Merge 2 - Update B;
Merge 3 - Update C;
END

another way I tried
BEGIN
Merge 1 - Update A;
END

BEGIN
Merge 2 - Update B;
END

BEGIN
Merge 3 - Update C;
END

In both the ways it executes only the 1st Merge statement.

Please suggests.

The original code given below

BEGIN
DECLARE @StageAgiliq _No1 INT=9;
DECLARE @StageAgiliq _No2 INT=25;
DECLARE @StageAgiliq _No3 INT=70;
BEGIN

MERGE table B AS TARGET  
USING table A AS SOURCE  
ON (TARGET.ID=SOURCE.ID)  
WHEN NOT MATCHED BY TARGET  
THEN INSERT ([ID],[Stage No])  
VALUES(SOURCE.[ID],@Stage_No1);  
  
  
MERGE table B AS TARGET  
USING table A AS SOURCE  
ON (TARGET.ID=SOURCE.ID)  
WHEN NOT MATCHED BY TARGET  
THEN INSERT ([ID],[Stage No])  
VALUES(SOURCE.[ID],@Stage_No2);  
  

MERGE table B AS TARGET  
USING table A AS SOURCE  
ON (TARGET.ID=SOURCE.ID)  
WHEN NOT MATCHED BY TARGET  
THEN INSERT ([ID],[Stage No])  
VALUES(SOURCE.[ID],@Stage_No3);  
END  

END

Thanks - Sarath

Answer 1

Maybe you need something like this:

insert TargetTableB(ID, [Stage No])
select ID, t.[Stage No] from SourceTableA
cross apply (values (@Stage_No1),(@Stage_No2), (@Stage_No3) ) as t([Stage No])
where ID not in (select ID from TargetTableB)

Answer 2

Hi @Sarath ，

Please provide create table DDL statement and some sample data so that we could proceed with further.

You could refer below example:

drop table if exists tableA  
 drop table if exists tableB  
      
 --DDL and sample data   
 create table tableA  
 (  
 ID int  
 )  
      
 create table tableB  
 (  
 ID int,  
 [Stage No] int  
 )  
      
 insert into tableA  values  
 (1),  
 (2),  
 (3),  
 (4)  
      
 insert into tableB  values  
 (1,9),  
 (1,25),  
 (1,70)  
  
 DECLARE @Stage_No1 INT=9;  
 DECLARE @Stage_No2 INT=25;  
 DECLARE @Stage_No3 INT=70;  
   
 MERGE INTO tableB T  
    USING (    
	SELECT  ID,A.Stage_No FROM TABLEA   
    CROSS APPLY (values (@Stage_No1),(@Stage_No2), (@Stage_No3) ) as A(Stage_No)) S  
       ON T.[ID] = S.[ID]  
 WHEN NOT MATCHED THEN  
    INSERT ( [ID], [Stage No] ) VALUES ( S.[ID],s.Stage_No  );  
  
 select * from tableB   

Output:
ID Stage No
1 9
1 25
1 70
2 9
2 25
2 70
3 9
3 25
3 70
4 9
4 25
4 70
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.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3

Hi @Sarath ，

If you would like to compare two tables and inserted the different data into second table, you could refer below:

drop table if exists tableA  
drop table if exists tableB  
  
--DDL and sample data   
create table tableA  
(  
ID int,  
[Stage No] int  
)  
  
create table tableB  
(  
ID int,  
[Stage No] int  
)  
  
insert into tableA  values  
(1,10),  
(2,9),  
(3,25),  
(4,70)  
  
insert into tableB  values  
(1,10)  
  
 MERGE tableB AS TARGET  
 USING tableA AS SOURCE  
 ON (TARGET.ID=SOURCE.ID)  
 WHEN NOT MATCHED BY TARGET  
 THEN INSERT ([ID],[Stage No])  
 VALUES(SOURCE.[ID],SOURCE.[Stage No]);  
  
select * from tableA  
select * from tableB   

If you really need to hardcode the 3 values, you could refer below:

DECLARE @Stage_No1 INT=9;  
DECLARE @Stage_No2 INT=25;  
DECLARE @Stage_No3 INT=70;  
  
MERGE INTO tableB T  
   USING ( VALUES ( 2, @Stage_No1 ),   
                  ( 3, @Stage_No2 ),  
				  ( 4, @Stage_No3 )) AS S ( [ID],[Stage No] )  
      ON T.[ID] = S.[ID]  
WHEN NOT MATCHED THEN  
   INSERT ( [ID], [Stage No] ) VALUES ( s.[ID],s.[Stage No]  );  

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.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 4

The MERGE statement would not work in your case since the first MERGE already insert data with the missing IDs. The second and third MERGE statements will do nothing because the tableB already has all IDs in TableA. Try this:

DECLARE @MissingIDsInTableB TABLE (  
 [ID] int  
);  
INSERT INTO @MissingIDsInTableB ([ID])  
SELECT [ID] FROM TableA WHERE [ID] NOT IN (SELECT [ID] FROM TableB);  
  
INSERT TableB([ID], [Stage No])  
SELECT [ID], @Stage_No1 FROM @MissingIDsInTableB;  
  
INSERT TableB([ID], [Stage No])  
SELECT [ID], @Stage_No2 FROM @MissingIDsInTableB;  
  
INSERT TableB([ID], [Stage No])  
SELECT [ID], @Stage_No3 FROM @MissingIDsInTableB;