Using the MERGE statement in SQL Server 2008 (and up)

The MERGE statement is a newly introduced functionality in SQL Server 2008, which, according to MSDN, Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

This came into the picture because I needed something to manipulate a table of all the (permanent) employees in the company which are pulled from another database that is being updated everyday. So instead of doing a rather more tedious script, I have decided to use the MERGE statement instead.

So for my example, I have two tables, one is called GALTemp and one is GAL, both with the same table schema. I am refreshing and updating GALTemp every night, and with that, I have to merge GALTemp with the GAL table. Merge in my case would mean:
-if the employee is already existing in the GAL table, some of its fields will be updated based from the data of GALTemp
-if the employee is a new one from GALTemp, it will be inserted as a new entry in GAL.

Also, to perform a MERGE, it also needs to have an identifier from both tables, such as an employee id that would refer to the data to be merged. In my case, similar to the JOIN clause, I am using the Alias_Name column which is the unique employee network ID in the database. If an Alias_Name from GALTemp matches with one row in GAL, it will update some of the fields (such as the employee's department, title, office, phone number, and company name). If an Alias_Name from GALTemp is not found in GAL, the whole row with that Alias_Name will be inserted as a new entry in the GAL table.

Take a look at my script below:

 USE TSRFv2_Copy  
 GO
  
 MERGE GAL AS tg1  
 USING (SELECT * FROM GALTemp) AS tg2  
 ON tg1.Alias_Name = tg2.Alias_Name  
 WHEN MATCHED THEN   
      UPDATE SET tg1.[Auto_ID] = tg2.[Auto_ID],  
                  tg1.[Title] = tg2.[Title],  
                  tg1.[Department] = tg2.[Department],  
                  tg1.[Office] = tg2.[Office],  
                  tg1.[Phone_Number] = tg2.[Phone_Number],  
                  tg1.[Company] = tg2.[Company]  
 WHEN NOT MATCHED THEN   
      INSERT   
       ([Auto_ID]  
       ,[Obj_Class]  
       ,[Display_Name]  
       ,[First_Name]  
       ,[Last_Name]  
       ,[Employee_ID]  
       ,[Alias_Name]  
       ,[Title]  
       ,[Department]  
       ,[Office]  
       ,[Phone_Number]  
       ,[E_mail]  
       ,[Company])  
    VALUES  
       (tg2.[Auto_ID]  
       ,tg2.[Obj_Class]  
       ,tg2.[Display_Name]  
       ,tg2.[First_Name]  
       ,tg2.[Last_Name]  
       ,tg2.[Employee_ID]  
       ,tg2.[Alias_Name]  
       ,tg2.[Title]  
       ,tg2.[Department]  
       ,tg2.[Office]  
       ,tg2.[Phone_Number]  
       ,tg2.[E_mail]  
       ,tg2.[Company]);  
 GO  

Do not forget to put a semicolon (;) at the end of the MERGE statement.

Happy Coding!