Cenário
Você possui um conjunto de dados A a ser inserido em uma tabela B no SQL Server, acontece que você não pode simplesmente fazer uma série de comandos INSERT pois esse conjunto de dados A possui campos que irão gerar conflito na chave primária da tabela B. Indo mais além, em caso de chave primária duplicada você deseja que os dados da tabela B sejam atualizados com as informações que estão vindo do conjunto de dados A. Indo ainda mais além, digamos que você queira eliminar as linhas da tabela B que não estejam constando no conjuntos de dados A. Seriam várias operações incluindo comandos INSERT, UPDATE e DELETE, concorda? Pois aqui vou explicar o funcionamento do comando MERGE, presente no SQL Server a partir da versão 2008 e como ele faz todas essas funções em uma única operação, otimizando muito a performance do servidor.
A título de comparação, temos algo parecido no Oracle, chamado UPSERT mas nosso foco aqui será a operação do SQL Server.
Como o comando possui muitas cláusulas tem-se a falsa impressão que é sua utilização é muito difícil, neste artigo vou mostrar como sua utilização básica é bem simples. O grande número de cláusulas, se deve ao fato que o comando faz várias operações. Então vamos direto ao assunto:
Sintaxe
O comando MERGE é bem simples de usar, ele se divide entre as seguintes cláusulas:
Exemplos em Itálico.
MERGE – Especifica a tabela destino.
MERGE Contatos AS Dest
USING – Especifica a fonte de dados de origem, pode ser uma tabela ou uma consulta. A fonte de dados pode ser uma tabela, ou uma consulta, conforme os exemplos abaixo:
USING Atualizar AS Orig
USING (SELECT Campo1, Campo2 FROM Tabela Atualizar WHERE Campo3 = ‘Atu’)
ON – Condição de ligação entre as duas tabelas.
ON (Dest.Id = Atualizar.Id)
WHEN MATCHED – O que fazer quando a condição de ligação está presente nas duas tabelas, ou seja, um registro com a mesma PK está presente nas duas tabelas, que poderia causar um erro de violação de chave primária.
WHEN MATCHED THEN
UPDATE SET Dest.Nome = Orig.Nome, Dest.Tel = Orig.Telefone
A cláusula WHEN MATCHED também pode ser utilizada para fazer comparação para que se faça atualização apenas de determinados registros.
WHEN MATCHED AND Dest.Tel <> Orig.Tel THEN
UPDATE SET Dest.Tel = Orig.Tel
WHEN MATCHED AND (Dest.Tel <> Orig.Tel OR Dest.CEP<>Orig.CEP) THEN
UPDATE SET Dest.Tel = Orig.Tel, Dest.CEP = Orig.CEP
WHEN NOT MATCHED BY TARGET – O que fazer quando o registro existe na tabela de origem mas não existe na tabela de destino, ou seja, a inserção pode ser feita sem risco de violação da PK.
WHEN NOT MATCHED BY TARGET THEN
INSERT (Nome, Telefone) VALUES (Orig.Nome, Orig.Telefone)
WHEN NOT MATCHED BY SOURCE – O que fazer quando o registro existe na tabela de destino mas não existe na tabela de origem. Para o caso de você querer excluir da tabela de destino os registros que não constam na tabela origem.
WHEN NOT MATCHED BY SOURCE THEN DELETE
OUTPUT – Tabela com o resultado da operação de Merge. A resposta do comando Merge é uma tabela contendo a lista dos registros das duas tabelas e as ações ocorridas, que podem ter sido INSERT, UPDATE ou DELETE. Esta tabela possui um campo chamado $action, tipo nvarchar(10), com o descritivo da ação. Você deve especificar quais campos a mais deseja que a tabela exiba. Os prefixos “INSERTED” e “DELETED” especificam tabelas de resultado, como no exemplo abaixo:
OUTPUT $action AS Acao, INSERTED.Id AS Inserido, DELETED.Id AS Eliminado
INTO – É possivel armazenar a tabela de resultados gerada pelo comando Output dentro do banco de dados. Antes de usar essa cláusula é necessário criar uma tabela com estrutura especificada pela cláusula Output. É útil para fazer consultas posteriores porém não é obrigatório. Essa cláusula é opcional.
INTO ResultadoDaSincronia
Para utilizar o comando Merge é necessário que estejam presentes, ao menos, uma das cláusulas When, sendo que todas podem estar presentes. A sintaxe completa do comando está disponível aqui:
MERGE (Transact-SQL) – SQL Server | Microsoft Docs
Exemplos
Exemplo 1:
Tabela existente “Contatos”:
SELECT id, Nome, Endereco, CEP FROM Contatos
Tabela com as alterações “Atualiza”:
CREATE TABLE Atualiza ( id int, Nome varchar(30), Endereco varchar(50), CEP varchar(8)) INSERT INTO Atualiza (id, Nome, Endereco, CEP) VALUES (1, 'Gabriel','Rua Dois-W','22795790'), (3, 'Myllena','Estr. dos Bandeirantes','22795050'), (4, 'Maria','Rua Des. Geraldo','22795030')
Executa sincronia entre as tabelas “Contatos” e “Atualiza”
-- Cria tabela resposta (opcional) CREATE TABLE Resposta ( Acao varchar(10), DestinoId int, OrigemId int ) -- Tabela destino MERGE Contatos AS Tgt -- Tabela origem USING Atualiza AS Src -- Ligação entre as duas tabelas ON (tgt.id = src.id) -- Quando registro existir nas duas, atualizar somente ém caso de mudança de CEP. WHEN MATCHED AND Tgt.CEP <> Src.CEP THEN UPDATE SET Tgt.Endereco = Src.Endereco, Tgt.CEP = Src.CEP -- Quando registro não existir no destino, inserir. WHEN NOT MATCHED BY TARGET THEN INSERT (Nome, Endereco, CEP) VALUES (Src.Nome, Src.Endereco, Src.CEP) -- Quando registro existir apenas no destino e não na origem, apagar. WHEN NOT MATCHED BY SOURCE THEN DELETE -- Resultado: OUTPUT $action AS Acao, DELETED.id AS DestinoId, INSERTED.id AS OrigemID -- Salva resultado na tabela "Resposta" INTO Resposta;
Depois de executar a query acima temos os seguintes resultados:
SELECT id, Nome, Endereco, CEP FROM Contatos SELECT Acao, DestinoId, OrigemId FROM Resposta
Exemplo 2:
No exemplo abaixo eu parti do seguinte pressuposto: Uma tabela com a seguinte estrutura preenchida com milhões de linhas.
CREATE TABLE cfmEventos ( [Id] [int] NOT NULL, [Esporte] [int] NOT NULL, [Data] [datetime] NOT NULL, [DataEpoch] [int] NOT NULL, [Liga] [int] NOT NULL, [Placar] [varchar](30) NOT NULL, [Time1] [int] NOT NULL, [Time2] [int] NOT NULL, [LigaNome] [varchar](100) NOT NULL, [Time1Nome] [varchar](100) NOT NULL, [Time2Nome] [varchar](100) NOT NULL)
Após conseguir os dados a partir de uma fonte externa para atualizar a tabela, escrevi o código de programação que gerava o seguinte conteúdo para ser enviado ao SQL Server:
-- Monta a tabela temporária para receber os dados de atualização CREATE TABLE #cfmEventosNew ( [Id] [int] NOT NULL, [Esporte] [int] NOT NULL, [Data] [datetime] NOT NULL, [DataEpoch] [int] NOT NULL, [Liga] [int] NOT NULL, [Placar] [varchar](30) NOT NULL, [Time1] [int] NOT NULL, [Time2] [int] NOT NULL, [LigaNome] [varchar](100) NOT NULL, [Time1Nome] [varchar](100) NOT NULL, [Time2Nome] [varchar](100) NOT NULL) -- Alimenta os dados de atualização na tabela temporária INSERT INTO #cfmEventosNew (Id, Esporte, DataEpoch, Data, Liga, Placar, Time1, Time2, LigaNome, Time1Nome, Time2Nome) VALUES (4599539, 1, 1643153400, '2022-01-25 23:30:00', 23596, '0-2', 1432, 6463, 'America Friendlies', 'Liverpool Montevideo', 'Instituto AC Cordoba'), (4440121, 1, 1643153400, '2022-01-25 23:30:00', 3570, '2-2', 79839, 7332, 'Brazil Campeonato Goiano', 'Aparecidense', 'Ipora EC'), (4440093, 1, 1643153400, '2022-01-25 23:30:00', 3570, '2-0', 79840, 74970, 'Brazil Campeonato Goiano', 'Anapolis FC', 'Goianesia'), (4438814, 1, 1643153400, '2022-01-25 23:30:00', 3584, '0-1', 195134, 518249, 'Brazil Campeonato Matogrossense', 'Sociedade Acao', 'Academia FC'), (4479349, 1, 1643152500, '2022-01-25 23:15:00', 1924, '0-1', 585785, 2066, 'Brazil Campeonato Sergipano', 'Falcon SE', 'Itabaiana'), (4533338, 1, 1643151900, '2022-01-25 23:05:00', 254, '3-0', 1492, 10034, 'Colombia Primera A', 'Independiente Santa Fe', 'Rionegro Aguilas'), (4488016, 1, 1643151600, '2022-01-25 23:00:00', 23911, '1-1', 358338, 44225, 'Mexico Liga de Expansion', 'Tepatitlan FC', 'Tlaxcala FC'), (4479064, 1, 1643151600, '2022-01-25 23:00:00', 2788, '0-0', 3159, 71241, 'Brazil Copa Nordeste', 'Nautico Capibaribe', 'Campinense Clube') -- Realiza o processo de inserção/atualização MERGE cfmEventos AS Tgt USING #cfmEventosNew AS Src ON (Tgt.Id = Src.Id) WHEN MATCHED THEN UPDATE SET Tgt.DataEpoch = Src.DataEpoch, Tgt.Data = Src.Data, Tgt.Placar = Src.Placar WHEN NOT MATCHED BY TARGET THEN INSERT (Id, Esporte, DataEpoch, Data, Liga, Placar, Time1, Time2, LigaNome, Time1Nome, Time2Nome) VALUES (Src.Id, Src.Esporte, Src.DataEpoch, Src.Data, Src.Liga, Src.Placar, Src.Time1, Src.Time2, Src.LigaNome, Src.Time1Nome, Src.Time2Nome) OUTPUT $Action AS Acao, INSERTED.Id AS Id;
No exemplo acima não é utilizada a operação DELETE porque a fonte de dados apenas insere novas linhas na tabela e, caso a linha já exista na tabela destino o conteúdo dela será atualizado.
O resultado dessa operação é uma tabela contendo as colunas “Acao” e “Id”, bem útil para fazer uma estatística com o total de registros da fonte de dados, quantos foram inseridos e quantos foram atualizados. Se fosse o caso teria mostrado também quantas linhas foram eliminadas.
Observação Importante:
O comando MERGE precisa ser finalizado com “;”. Caso isso não seja feito a query será encerrada com erro:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Exemplo 3:
Digamos que a fonte de dados tivesse todo o conteúdo da tabela e que a partir disso quiséssemos fazer uma sincronia completa da fonte de dados com a tabela destino, inclusive excluindo da tabela destino as linhas que não existem na fonte de dados. E para completar este segundo exemplo nós vamos salvar o log da operação na tabela “ResultadoSincronia”. O código seria o seguinte:
CREATE TABLE ResultadoSincronia ( Acao nvarhar(10), Id int ) MERGE cfmEventos AS Tgt USING #cfmEventosNew AS Src ON (Tgt.Id = Src.Id) WHEN MATCHED THEN UPDATE SET Tgt.DataEpoch = Src.DataEpoch, Tgt.Data = Src.Data, Tgt.Placar = Src.Placar WHEN NOT MATCHED BY TARGET THEN INSERT (Id, Esporte, DataEpoch, Data, Liga, Placar, Time1, Time2, LigaNome, Time1Nome, Time2Nome) VALUES (Src.Id, Src.Esporte, Src.DataEpoch, Src.Data, Src.Liga, Src.Placar, Src.Time1, Src.Time2, Src.LigaNome, Src.Time1Nome, Src.Time2Nome) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $Action AS Acao, INSERTED.Id AS Id INTO ResultadoSincronia ;
Características do Comando Merge
- Não esquecer de finalizar com “;” ou vai receber erro 10713.
- Quanto usando imeadiatamente após o comando Merge, @@RowCount entrega o número total de registros afetados pelas operações de CRUD.
- Ao menos uma cláusula Match tem que ser usada, e elas podem ser colocadas em qualquer ordem.
- Uma variável não pode ser atualizada dentro de uma cláusula Match.
- O comando Merge aumenta muito a perfomance, uma vez que faz todas a operações de uma única vez.
- O comando Merge utiliza o mesmo tipo de lock que “If Exists” menos Intent Shared (IS).
- Para cada comando INSERT, UPDATE OU DELETE o BD irá disparar os After Triggers correspondentes na tabela destino mas não há garantia de qual trigger será disparado primeiro.
