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

The full documentation is available from Microsoft website: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017

One thing that you need to careful about is specifying columns for the target table, according to Microsoft article:

That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause

Usually fine-tuning merge queries seems to be more complicated for at least beginner to intermediate developers, but as long as the total number of columns in the first query match the merge query, you should be fine.

Last but not least, you need the following permissions:

• SELECT permission on the source table to be able to run the query
• INSERT, UPDATE, or DELETE on the target table