insert or update between two table

Question

below is two table,

my requirement is that ,when value exists in table #table1 and not exists in table #table2 ,then insert or if enrollNumber and A_Date exists in table #table2 then values get update.

 Create table #table1 (t1_ID int,EnrollNumber int,A_Date date,In_Time time(7),out_Time time(7))  
          
                  Insert into #table1 values (111,10001,'2022-10-10','08:04:00','20:04:00')  
                 
                  Insert into #table1 values (222,10001,'2022-10-11','08:04:00','21:04:00')  
                  Insert into #table1 values (333,10002,'2022-10-12','09:04:00','20:04:00')  
          
				  Insert into #table1 values (444,10001,'2022-10-13',null,'17:45:00')  
                  Insert into #table1 values (555,10005,'2022-10-13','9:15','21:00:00')  
           
  
   Create table #table2  (t2_ID int, EnrollNumbeR int,A_Date date,In_Time time(7),out_Time time(7))  
               
			      Insert into #table2 values (211,10001,'2022-10-10','08:04:00','20:04:00')  
                 
                  Insert into #table2 values (212,10001,'2022-10-11',null,null)  
                  Insert into #table2 values (213,10002,'2022-10-12',null,'20:04:00')  
          
				  Insert into #table2 values (214,10001,'2022-10-13','7:00',null)  
  
  
Current table status  

Expected Output, which i highlighted in red

Answer 1

Hi @Analyst_SQL ,

check this query:

MERGE #table2 AS Target  
USING (select ROW_NUMBER()over(order by t1.A_Date)+210 as [t2_ID]  
,t1.EnrollNumbeR,t1.A_Date, case when t1.In_Time is not null then t1.In_Time else t2.In_Time end as [In_Time],t1.out_Time  
from #table1 t1 left join #table2 t2 on t2.EnrollNumber =t1.EnrollNumbeR and t2.A_Date = t1.A_Date  
)  
 AS Source  
ON Source.EnrollNumbeR = Target.EnrollNumbeR and Source.A_Date = Target.A_Date  
WHEN NOT MATCHED BY Target THEN  
    INSERT (t2_ID , EnrollNumbeR ,A_Date ,In_Time ,out_Time)   
    VALUES (Source.t2_ID,Source.EnrollNumbeR, Source.A_Date,Source.In_Time,Source.out_Time)  
WHEN MATCHED THEN UPDATE SET  
        Target.In_Time	= Source.In_Time,  
        Target.out_Time	= Source.out_Time;  
  
select * from #table2  

Best regards
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.