SQL Server: How to customize Merge When Matched area

Question

See my code where i am insert or updating 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 is empty then that records will be removed from 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 is empty then that records will be removed from the table.

please guide me how to achieve this. thanks

Answer 1

Check if this works as expected:

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 
    delete  
When Matched Then 
    update set Trg.Marks=Src.Marks  
when not matched then  
    insert (Name,Marks) values (Src.Name,Src.Marks)
    OUTPUT $action, 
        case $action when 'delete' then deleted.Name else inserted.Name end, 
        case $action when 'delete' then deleted.Marks else inserted.Marks end
    INTO @archive;

Answer 2

Please try the following solution.
It takes care for special cases when the <marks> element value is missing as well as when that element is not present at all in the XML.

SQL

USE tempdb;
GO

DROP table if exists Student

Declare @Data xml = 
N'<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);

-- before
SELECT * FROM dbo.Student;

DECLARE @archive TABLE
(
 ActionType  varchar(10),
 Name varchar(10),
 Marks int
);

Merge into Student as Trg  
Using (
 SELECT d.x.value('(Name/text())[1]','varchar(20)') as Name
 , NULLIF(d.x.value('(Marks/text())[1]','int'), 0) as Marks 
 FROM @data.nodes('/Root/Student') AS d(x)
) as Src  
on Trg.Name=Src.Name  
WHEN Matched AND Src.Marks IS NOT NULL THEN 
UPDATE 
SET Trg.Marks=Src.Marks  
WHEN NOT MATCHED  then  
INSERT (Name,Marks) values (Src.Name,Src.Marks)
WHEN Matched AND Src.Marks IS NULL THEN 
DELETE
OUTPUT
    $action ,
    inserted.*
INTO @archive;

-- after
SELECT * FROM dbo.Student;

Answer 3

Hi @Sudip Bhatt ，

Please also check another method：

    drop table if exists Student  
    create table Student (  
    Name varchar(10),  
    Marks int)  
    insert into Student values('Rakesh',90),('Mahesh',80),('Jack',80),('Manoj',null)  
             
    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>'                   
    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 --and Trg.marks is not null   
    When Matched and Trg.marks is not null Then   
    update   
    set  Trg.Marks=Src.Marks   
    When Matched and Trg.marks is null Then delete  
    when not matched  then    
    insert (Name,Marks) values (Src.Name,Src.Marks)  
    OUTPUT  
    $action ,  
    inserted.*  
    INTO @archive;  
      
    select* from  Student  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

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 November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table