How to use the merge statement in sql server

Question

I would like to know how to use the instruction MERGE. When to use and what advantages and disadvantages to use?

Answer 1

By definition the command MERGE:

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

Summary:

But simplified with merge you can configure comparisons between two data sources (Source and Destination) and set instructions(Insert, update, delete) to run according to the comparison result.

How it works:

MERGE => Sets 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 origin of the data.

USING => Specifies the origin of the data and based on the conditions of the clause ON, Checks the relation 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 in your data.

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

WHEN MATCHED or WHEN NOT MATCHED => Determines which action will be performed at the destination, at the end of the instruction always comes accompanied by the term THEN. In this clause, it is possible to insert filters as we do in a WHERE, with the aim of restricting the possibilities and leaving the most accurate and granular operation possible. One can use ANDs or ORs in how many occurrences of WHEN were necessary.

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 above example I compare the user table of one database to the other, if the source data is changed (I identify this through the change date) then update the data at the destination, if there is no user in the destination seguinifica that is a new user and therefore I create it in the destination.

When to use:

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

Basically whenever you identify the need to compare two data sources to then manipulate them then you have identified a situation where if Can make use of the merge.

Sources:

• Microsoft
• Devmedia
• Sqlgo