insert / update Merged

Question

Create table1
( id, region string,subregion string, value1 int,value2 int,value2 int,value3 int)
Insert into table1
values('1','west','coastal',22,34,44),
('2','north','northsea',23,14,24)

Create table2
( id, region string,subregion string, value1 int,value2 int,value2 int,,value3 int)
Insert into table2
values('1','west','coastal',21,34,44),
('2','north','northsea',23,15,24)

how we can use different methods in insert and update statement. means if values match then need not insert but update the records. and also expect scenario to maintain changes like history and also merge statement

Answer 1

Hi @Shambhu Rai

if values match then need not insert but update the records. and also expect scenario to maintain changes like history

It seems you need a MERGE statement with OUTPUT clause, like this:

MERGE #table1 T  
USING #table2 S ON T.id=S.id  
   
WHEN MATCHED THEN  
UPDATE SET value1=S.value1, value2=S.value2, value3=S.value3  
  
WHEN NOT MATCHED BY TARGET   
THEN INSERT (id,region,subregion,value1,value2,value3)   
     VALUES (S.id,S.region,S.subregion,S.value1,S.value2,S.value3)  
  
OUTPUT DELETED.*, $action AS [Action], INSERTED.* ;  

Refer to this article for more details: SQL Server MERGE Statement overview and examples

Best regards,
LiHong