SQL Server: How to customize Merge When Matched area

user_14631743 0
Viewed 48

See my code where I insert or update a table from XML using merge statement.

drop table if exists Student
       
  Declare @Data xml  
             
   set @Data=  
   '<Root>  
   <Student>  
   <Name>Rakesh</Name>  
   <Marks>80</Marks>  
   </Student>  
   <Student>  
   <Name>Mahesh</Name>  
   <Marks>90</Marks>  
   </Student>  
   <Student>  
   <Name>Gowtham</Name>  
   <Marks>60</Marks>  
   </Student>  
   <Student>  
   <Name>Manoj</Name>  
   <Marks></Marks>  
   </Student>  
   </Root>'  
         
    create table Student (
    Name varchar(10),
    Marks int
    )
       
    insert into Student values
    ('Rakesh',90),
    ('Mahesh',80),
    ('Jack',80),
    ('Manoj',57)
       
  DECLARE @archive TABLE
  (
     ActionType  varchar(10),
     Name varchar(10),
     Marks int
  );
       
   Merge into Student as Trg  
   Using (select d.x.value('Name[1]','varchar(20)') as Name ,  
   d.x.value('Marks[1]','int') as Marks from  
   @data.nodes('/Root/Student')as d(x)) as Src  
   on Trg.Name=Src.Name  
   When Matched Then update set  
   Trg.Marks=Src.Marks  
   when not matched  then  
   insert (Name,Marks) values (Src.Name,Src.Marks)
   OUTPUT
     $action ,
     inserted.*
  INTO @archive;

I want when Name match then UPDATE will perform but when Name match but Marks is empty then that records will be deleted based on name matched.

so tell me how could I customize this.

see my XML there is one student named Manoj whose marks are empty then that records will be removed from the table based on name matched.

How could I mention a condition in Merge that when Name matched and marks not empty then update and when Name matched but marks are empty then that records will be removed from the table.

Can i use Multiple matched condition ? if yes then it will be possible.

please guide me on how to achieve this. thanks

tsql
1 Answers

You should be able to use multiple WHEN MATCHED parts each with a clause search condition. One search checks that Src.Marks is not zero and the other checks that it was.

I've also put a clause search condition on the NOT MATCHED part as I would guess you don't want to be inserting people that have no marks either.

MERGE INTO Student as Trg  
USING (
    SELECT 
        d.x.value('Name[1]','varchar(20)') AS Name ,  
        d.x.value('Marks[1]','int') AS Marks 
    FROM  
        @data.nodes('/Root/Student') AS d(x)
) AS Src  
ON Trg.Name=Src.Name  
WHEN MATCHED AND Src.Marks <> 0 THEN 
    UPDATE 
    SET  
        Trg.Marks=Src.Marks  
WHEN MATCHED AND Src.Marks = 0 THEN
    DELETE
WHEN NOT MATCHED AND Src.Marks <> 0 THEN  
   INSERT (Name,Marks) 
   VALUES (Src.Name,Src.Marks)
   OUTPUT
     $action,
     inserted.*
  INTO @archive;
Martin_Brown_205530
Related