How to use the merge statement in sql server

By definition the MERGE command:

  

Performs insert, update, or delete operations on a table   based on the results of the join with the source table.   For example, you can synchronize two tables by inserting, updating   or deleting rows in a table based on the differences found   in the other table.

Summary:

In a simplified way with merge you can configure comparisons between two data sources (Source and destination) and set instructions (insert, update, delete) to execute according to the result of the comparison.

How it works:

MERGE = > Defines the destination that will receive the processed data, this destination can be understood as a Table or a View. All changes will occur at this destination regardless of the change or source of the data.

USING = > Specifies the source of the data and based on the conditions of the ON clause, checks the relationship of the source data to the destination. This origin can be determined by a Table, a View, or even a Function. It will be used as a basis to change the destination and will not suffer any changes to your data.

ON = > Specifies the condition of comparison of tables and follows the same concept of ON used in JOIN .

WHEN MATCHED or WHEN NOT MATCHED = > Determines which action will be performed on the target, at the end of the instruction always comes with the term THEN . In this clause, it is possible to insert filters as we do in a WHERE , in order to restrict the possibilities and allow the most accurate and granular operation possible. You can use ANDs or ORs in how many instances of WHEN are required.

Example:

MERGE db1.Usuario AS Destino

USING db2.Usuario AS Origem -- A origem pode ser uma tabela, uma consulta..

ON Destino.CPF = Origem.CPF

-- Quando houver registros em ambas com mesmo CPF e a data de alteração da 
-- origem for maior que a data de alteração da destino fará um update..
WHEN MATCHED AND Origem.DataAlteracao > Destino.DataAlteracao THEN 
THEN 
  UPDATE SET Senha = Origem.Senha
  , DataAlteracao = Origem.DataAlteracao

-- Quando não houver registros em ambas com mesmo CPF
-- Inserirá o registro no Destino
WHEN NOT MATCHED
THEN 
    INSERT (CPF, Login, Senha, DataAlteracao) 
    VALUES (Origem.CPF, Origem.Login, Origem.Senha, Origem.DataAlteracao)

In the example above I compare the user table of one database with the other, if the data of the source is changed (I identify it through the change date), then I update the data in the destination, if there is no user in the destination, which is a new user and therefore I create it in the destination.

When to use:

The word duty here is a bit complicated, as everything varies according to your need, your business model, your database.

Basically whenever you identify the need to compare two data sources and then manipulate them, then you have identified a situation where You can use merge .

Fonts :

• Microsoft
• Devmedia
• SqlGo