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