Insert to first table based on another table

As suggested by @Larnu you can use MERGE in sql server:

Schema and insert statements

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 ); 

Merge Query:

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); 

After merge:

 select * from users; 

Output:

db<>fiddle here

If you want to merge peoples with role 1 only:

Schema and insert statements:

 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 ); 

Merge query:

 MERGE Users AS TARGET USING (select * from people where role=1) 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 , TARGET.role=SOURCE.role --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); 

After MERGE:

 select * from users; 

Output:

db<>fiddle here