SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table people( id int ,f_name  varchar(25), l_name varchar(25),role  int);
insert into people values( 1    ,'John', 'Nathan', 1);     
insert into people values( 2    ,'Brand','Ba'    , 1);     
insert into people values( 3    ,'Bob',  'Do'    , 2);     
insert into people values( 4    ,'Alice','Sia'   , 1);     


create table Users( id int,  f_name varchar(25), l_name varchar(25), role int);
insert into Users values( 1    ,'John' ,'Tom' ,1 );
5 rows affected

MERGE Users AS TARGET
USING people AS SOURCE 
ON (TARGET.id = SOURCE.id) 
--When records are matched, update the records 
WHEN MATCHED 
THEN UPDATE SET TARGET.f_name = SOURCE.f_name, TARGET.l_name = SOURCE.l_name 
--When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET 
THEN INSERT (id, f_name, l_name,role) VALUES (SOURCE.id, SOURCE.f_name, SOURCE.l_name, SOURCE.role);
4 rows affected

select * from users;


id
f_name
l_name
role


1
John
Nathan
1

2
Brand
Ba
1

3
Bob
Do
2

4
Alice
Sia
1