[Resolvido] utilizando o comando Merge

[eriva_br 7]

O comando TSQL MERGE é uma novidade do sql 2008,

 

O MERGE permite verificações entre as tabelas de origem e destino e promete melhor desempenho na utilização de INSERT, UPDATE e DELETE em certos casos.

 

Obs.: as tabelas não necessitam ter exatamente a mesma estrutura, claro que se tiver melhor será para a resolução do problema.

 

Sua criação é relativamente simples, seus principais 'sub-comandos' são:

MERGE: define a tabela alvo da operação, a tabela principal, o destino dos dados.

USING: define a origem dos dados, pode ser tabela, view ou sub-query

ON: realiza a ligação das tabelas

e suas principais ações: (no comando não é obrigatório que se use os 3, pode-se usar somente 1 ou 2 deles)

WHEN MATCHED: quando encontra os dados nas duas tabelas, nesse caso por exemplo podes fazer um update em alguns campos na tabela destino

WHEN NOT MATCHED BY TARGET: quando os dados existem na tabela origem, mas não são encontrados na tabela destino, nesse caso por exemplo podes fazer um insert na tabela destino com os dados da tabela origem

WHEN NOT MATCHED BY SOURCE: o inverso, ou seja, quando os dados existem na tabela destino, mas não são encontrados na tabela de origem, nesse caso por exemplo podes fazer um delete na tabela destino

Obs.: podemos adicionar condições extras juntos com as opções WHEN, por exemplo "WHEN NOT MATCHED BY TARGET and source.saldo > 750 THEN"

 

agora um exemplo prático para melhor entendimento.

 

tabelas para testes:

create table tb_cliente (id int primary key, nome varchar(30), saldo money, dt_modificacao date)
insert tb_cliente (id, nome, saldo, dt_modificacao ) values (1, 'Brazil', 1000, null)
insert tb_cliente (id, nome, saldo, dt_modificacao ) values (2, 'Espanha', 2000, null)
insert tb_cliente (id, nome, saldo, dt_modificacao ) values (3, 'France', 3000, null)

create table tb_novos_clientes (id int primary key, nome varchar(30), saldo money)
insert tb_novos_clientes (id, nome, saldo) values (1, 'Brazil', 1500)
insert tb_novos_clientes (id, nome, saldo) values (2, 'Spain', 3000)
insert tb_novos_clientes (id, nome, saldo) values (4, 'England', 800)
insert tb_novos_clientes (id, nome, saldo) values (5, 'Argentina', 700)
insert tb_novos_clientes (id, nome, saldo) values (6, 'Germany', 80)

o comando MERGE:

MERGE tb_cliente AS target
USING (select * from tb_novos_clientes where saldo > 100) AS source --ou então poderiamos colocar somente a tabela tb_novos_clientes
ON (target.id = source.id)
WHEN MATCHED THEN 
UPDATE SET dt_modificacao = GETDATE(),
nome = source.nome,
saldo = source.saldo
WHEN NOT MATCHED BY TARGET and source.saldo > 750 THEN     
INSERT (id, nome, saldo)
VALUES (id, nome, saldo)
WHEN NOT MATCHED BY SOURCE THEN
DELETE 
OUTPUT INSERTED.*, $action, DELETED.*;

o antes e o depois, com dados que sofreram alterações e novos dados, retornados com ajuda do nosso grande amigo OUTPUT, isso você pode colocar por exemplo em uma outra tabela de log.

id   nome    saldo   dt_modific $actio id   nome    saldo   dt_mod
---- ------- ------- ---------- ------ ---- ------- ------- ------
1    Brazil  1500,00 2010-05-30 UPDATE 1    Brazil  1000,00 NULL
2    Spain   3000,00 2010-05-30 UPDATE 2    Espanha 2000,00 NULL
NULL NULL    NULL    NULL       DELETE 3    France  3000,00 NULL
4    England  800,00 NULL       INSERT NULL NULL    NULL    NULL
(4 row(s) affected)

notamos que:

- os registros 1-Brazil (campo saldo) e 2-Espanha (campos nome e saldo) foram alterados

- o registro 4-England foi incluído

- O registro 5-Argentina não foi incluído, pois tem saldo de 700 e colocamos a condição Saldo > 750 no comando WHEN NOT MATCHED BY TARGET

- O registro 6-Germany não foi incluido pois ele tem saldo de 80 e na opção USING adicionamos um sub-select filtrando saldo > 100

- o registro 3-França foi excluído pois não estava na tabela origem

 

e por final nossa tabela principal, a de clientes, ficou assim:

id      nome         saldo     dt_modificacao
------- ------------ --------- --------------
1       Brazil       1500,00   2010-05-30
2       Spain        3000,00   2010-05-30
4       England      800,00    NULL
(3 row(s) affected)

 

Verificamos que o comando MERGE em alguns casos pode resolver problemas de comparações e cargas entre tabelas rapidamente e com menos esforço que em versões anteriores do sql server, onde tinhamos que utilizar cursores e outros recursos alternativos.

 

 

abs

 

AUTOR: "eriva_br"

 

Dúvidas, criticas, contribuições, correções e adições serão bem vindas.