2022-11-14

Why "merge delete" deleting entire target table in SQL Server

I was writing a stored procedure upsert on a table.

Here is my stored procedure:

create PROCEDURE [dbo].[ABC] 
(              
 @abc [dbo].[ABCTable]   readonly      
 )              
AS              
BEGIN              
 SET NOCOUNT ON;              
              
 MERGE INTO dbo.ABC trg              
 USING @abc src              
  ON src.a = trg.a 
  and src.b=trg.b  
  and src.c = trg.c
  and src.d = trg.d  
  and src.e = trg.e  
  and src.f = trg.f  
  and src.g =trg.g  
 WHEN MATCHED            
  THEN              
   UPDATE              
   SET                          
   h = src.h            
 WHEN Not MATCHED  BY SOURCE   
  THEN              
   DELETE              
 WHEN NOT MATCHED BY TARGET             
  THEN              
   INSERT (              
    a,            
    b,            
    c,            
    d,            
    e,            
    f,
    g,
    h            
    )              
   VALUES (              
    a,            
   b,            
 c,            
 d,            
 e,            
 f,            
 g            
 ,h            
    );                             
END 

Here is the data

ABC 
a b c d  e   f  g   h
1 2 3 4  5   6  7   8
1 2 9 10 11  12 13  14

@abc
a b c d  e   f  g   h
3 4 5 6  7   8  9   10
1 2 3 4  5   6  7   9

Final output as :

ABC 
a b c d  e   f  g   h
1 2 3 4  5   6  7   9
1 2 9 10 11  12 13  14
3 4 5 6  7   8  9   10

However my stored procedure is deleting the existing rows and adding new one. How can I resolve this?



No comments:

Post a Comment