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:

http://msdn.microsoft.com/en-us/library/bb510625.aspx