From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done.

Syntax

MERGE <target_table>
USING <table_source>
ON <Search>
WHEN MATCHED THEN UPDATE SET <set_clause>
WHEN NOT MATCHED THEN INSERT VALUES (<values_list>)

Create two tables and Insert some records

CREATE TABLE TargetTable([Id] INT, [Name] NVARCHAR(50))

CREATE TABLE SourceTable([Id] INT, [Name] NVARCHAR(50))

INSERT INTO TargetTable(Id,Name)
VALUES(1, 'John'), (2, 'Mezi'), (3, 'Tom')

INSERT INTO SourceTable(Id,Name)
VALUES(1, 'JohnNew'), (4, 'Peter'), (3, 'Tom-new')

Then do Merge

MERGE TargetTable AS T
USING SourceTable AS S
ON T.Id = S.Id
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN INSERT VALUES (S.Id, S.Name);

Now view TargetTable Data