Knowledge : Using 'MERGE' functionality to sync two different tables.

• 'MERGE' is a sql command introduced with SQL Server 2008 which makes ease of inserting & updating records between source and target tables. This is similar to a command in Oracle which is called 'UPSERT'. 
• Though it causes a little penalty in performance but its a very reliable and powerful command. Though it has slight impact in performance it is more powerful & robust command when compared to the traditional way of writing INSERTs, UPDATESs & DELETEs separately as with this command selextion happens only once.
• There are 3 separate matching clauses you can specify. The 3 matching clauses are:
• WHEN MATCHED
• WHEN [TARGET] NOT MATCHED
• WHEN SOURCE NOT MATCHED
• This command is so flexible with many options like you can specify constraints along with 'MATCHED' or 'UNMATCHED' clause as below,
• WHEN MATCHED AND order_id> 1234
• It is acceptable to have multiple instances of same matching clause without any restrictions.
• We can have $action function which outputs the type of action performed like INSERT, UPDATE or DELETE.
• This is very useful command to sync two or more tables,

Example:

CREATE TABLE temp_to_load_all_chnages(
  id INT,
  fname VARCHAR(10),
  lname VARCHAR(10`),
  dob datetime,
  action_taken VARCHAR(20),
  NEW_id INT,
  NEW_fname VARCHAR(10),
  NEW_lname VARCHAR(10`),
  NEW_dob datetime,
    );

MERGE sqlcheckpoint_target_db..customer_details AS target
    USING (SELECT 
    id,
    fname,
    lname,
    dob
FROM sqlcheckpoint_source_db..customer_details) AS source 
    ON (target.id = source.id)
    WHEN MATCHED THEN 
        
        UPDATE SET
  id = source.id
  ,fname=source.fname
  ,lname = source.lname
  ,dob = source.dob
        
 WHEN NOT MATCHED BY TARGET THEN 
     INSERT (
     id,
     fname,
     lname,
     dob
    )
     VALUES (
           source.id,
     source.fname,
     source.lname,
     source.dob
     )
   WHEN NOT MATCHED BY SOURCE THEN
   DELETE 
    OUTPUT deleted.*, $action, inserted.* INTO temp_to_load_all_chnages;

For syntax & more details please refer msdn link - click here to redirect to msdn