Merge records in SQL Server
Sometime multiple records have to be compared and somehow merged to update one record. Logically there should be some kind of for loop to go through all the records and find those matching and then do some update. Obviously it’s an expensive process.
Luckily there is an alternative:
According to Microsoft, Merge Performs insert, update, or delete operations on a target table based on the results of a join with a source table.
The following example has a source and target table and try to find some matches and perform update.
MERGE dbo.EmployeeAddress AS Target USING (select Employee_id, Street, City, [State] from EmployeeImport EI) AS Source ON (Target.Employee_id = Source.Employee_id ) WHEN MATCHED THEN UPDATE SET Target.Street = Source.Street, Target.City = Source.city, Target.[State] =Source.[State] WHEN NOT MATCHED BY TARGET THEN INSERT(Employee_id, Street, City, [State]) VALUES( Source.Employee_id, Source.Street, Source.City, Source.[State])
More information on Microsoft MSDN: