SQLServerLogo

Rotinas para a sincronização de dados em bancos relacionais costumam ser implementadas através do uso extensivo de instruções DML como INSERT, UPDATE e DELETE. Exemplos comuns deste tipo de tarefa incluem procedimentos para a atualização de listas de preços de produtos, lançamentos contábeis e/ou financeiros, apontamentos com horários de entrada e saída de funcionários, dentre outras formas de informação.

Este trabalho envolve geralmente a manipulação de múltiplos registros, não sendo raros os casos em que o código gerado faz uso de estruturas condicionais, loops e cursores. A intenção por trás disto é efetuar iterações em um conjunto de valores e, a partir de uma série de condições pré-definidas, proceder com a inclusão, alteração ou exclusão de linhas em uma tabela.

Procurando simplificar este processo de carga de informações, a equipe de desenvolvimento do SQL Server introduziu a partir da versão 2008 deste SGBD um novo recurso à extensão T-SQL: trata-se da instrução MERGE. O objetivo deste post é demonstrar como este comando pode ser utilizado na atualização de tabelas dentro do SQL Server; para isto será utilizado um exemplo em que, a partir de uma tabela com dados armazenados de forma provisória, uma lista de produtos será alterada (com a modificação de itens já existentes e inclusão de novos registros).

Exemplo de utilização do comando MERGE

O exemplo apresentado a seguir fará uso de duas tabelas:

  • Uma primeira estrutura chamada “TMP_Produtos”, a qual armazenará preços de produtos de maneira temporária. Em um processo hipotético, o preenchimento desta tabela aconteceria a partir de um procedimento de carga de arquivos enviados por fornecedores;
  • Uma tabela definitiva chamada “Produtos”, em que constará uma listagem consolidada com todos os produtos comercializados por uma empresa.

OBSERVAÇÃO: Todos os testes descritos nesta seção foram executados em uma base criada em um servidor SQL Server 2012, a partir da ferramenta SQL Server Management Studio.

Na Listagem 1 está o script para a criação das tabelas TMP_Produtos e Produtos.

Conforme é possível observar, a primeira destas estruturas (TMP_Produtos) é extremamente simples, não dispondo de chaves ou outras restrições. Num cenário real até dados com com inconsistências seriam carregados nesta tabela, com um processo de validação determinando quais registros poderiam ou não ser carregados na estrutura definitiva.

Já a tabela Produtos conta com uma chave primária que é um campo auto incremento (IdProduto), além de uma chave única que representa uma chave alternativa para os registros armazenados (campo CodigoBarras). Foram também criados campos para preenchimento da data de inclusão dos produtos na base (campo DataInclusao), assim como quando os dados de tais itens passaram por sua atualização (campo DataAtualizacao).

CREATE TABLE [dbo].[TMP_Produtos](
	[CodigoBarras] [char](13) NOT NULL,
	[NomeProduto] [varchar](50) NOT NULL,
	[Categoria] [varchar](20) NOT NULL,
	[PrecoVenda] [decimal](12,2) NOT NULL
)
GO

CREATE TABLE [dbo].[Produtos](
	[IdProduto] [int] IDENTITY(1,1) NOT NULL,
	[CodigoBarras] [char](13) NOT NULL,
	[NomeProduto] [varchar](50) NOT NULL,
	[Categoria] [varchar](20) NOT NULL,
	[PrecoVenda] [decimal](12,2) NOT NULL,
	[DataInclusao] [datetime] NOT NULL,
	[DataAtualizacao] [datetime] NULL,
 CONSTRAINT [PK_Produtos] PRIMARY KEY ([IdProduto]),
 CONSTRAINT [UK_Produtos_CodigoBarras] UNIQUE ([CodigoBarras])
)
GO

Listagem 1: Scripts para criação das tabelas TMP_Produtos e Produtos

Um segundo script (Listagem 2) deverá ser executado, de forma a possibilitar a demonstração dos efeitos do comando MERGE em uma tabela (Produtos) com registros já cadastrados.

INSERT INTO dbo.Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda, DataInclusao)
VALUES('7890000000111', 'Iron Maiden - Powerslave', 'CDs', 44.90, GETDATE())

INSERT INTO dbo.Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda, DataInclusao)
VALUES('7890000000222', 'Metallica - Black Album', 'CDs', 39.95, GETDATE())

INSERT INTO dbo.Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda, DataInclusao)
VALUES('7890000000333', 'Invictus', 'DVDs', 16.90, GETDATE())

INSERT INTO dbo.Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda, DataInclusao)
VALUES('7890000000444', 'Uma Mente Brilhante', 'DVDs', 29.90, GETDATE())

INSERT INTO dbo.Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda, DataInclusao)
VALUES('7890000000555', 'Trilogia Senhor dos Anéis', 'Blu-Ray', 129.90, GETDATE())

Listagem 2: Scripts inicial para preenchimento da tabela Produtos

Executando uma consulta à tabela Produtos (como indicado na Listagem 3), será possível observar um resultado similar àquele da Imagem 1.

SELECT IdProduto,
       CodigoBarras,
       NomeProduto,
       Categoria,
       PrecoVenda,
       DataInclusao,
       DataAtualizacao
FROM dbo.Produtos

Listagem 3: Consulta à tabela Produtos

Imagem01
Imagem 1. Resultado de uma consulta à tabela Produtos

Na Listagem 4 é apresentado um script para carregamento de dados na tabela TMP_Produtos. O objetivo neste último caso é simular um processo em que dados são carregados de uma fonte externa (um arquivo enviado por um fornecedor, por exemplo), para se proceder posteriormente com a atualização do cadastro de produtos.

INSERT INTO dbo.TMP_Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda)
VALUES('7890000000222', 'Metallica - Black Album', 'CDs', 46.95)

INSERT INTO dbo.TMP_Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda)
VALUES('7890000000444', 'Uma Mente Brilhante', 'DVDs', 32.75)

INSERT INTO dbo.TMP_Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda)
VALUES('7890000000660', 'Box Star Wars - 6 episódios', 'Blu-Ray', 299.90)

INSERT INTO dbo.TMP_Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda)
VALUES('7890000000777', 'A Arte da Guerra', 'Livros', 10.00)

INSERT INTO dbo.TMP_Produtos(CodigoBarras, NomeProduto, Categoria, PrecoVenda)
VALUES('7890000000888', 'Transformando Suor em Ouro', 'Livros', 24.90)

Listagem 4: Carregando dados na tabela TMP_Produtos

O próximo passo agora consistirá na execução do comando MERGE, de maneira a se atualizar o cadastro de produtos com as informações existentes na tabela TMP_Produtos. Na Listagem 5 está o código que tornará possível tais ajustes:

  • No início da instrução MERGE é indicada qual estrutura será atualizada (neste caso específico, a tabela Produtos);
  • A cláusula USING indica em qual tabela estão os dados utilizados no processo de atualização (TMP_Produtos para este exemplo). Este elemento é acompanhado da cláusula ON, local este em que se efetua a ligação entre as tabelas de origem e destino;
  • Se a condição definida na cláusula ON não seja verdadeira, o bloco iniciado por WHEN NOT MATCHED é executado. Para o exemplo aqui abordado, isto acontecerá quando um código de barras ainda não constar na tabela Produtos (em tais casos, um comando INSERT será executado de forma a se cadastrar um novo produto nesta estrutura);
  • Se o código de barras já existir, será então executado o bloco iniciado por WHEN MATCHED. O objetivo deste conjunto de instruções é atualizar os dados de um registro já existente na tabela Produtos;
  • Múltiplas cláusulas WHEN NOT MATCHED e WHEN MATCHED podem ser associadas a um bloco de comandos iniciado por MERGE. Quando isto acontecer, condições lógicas (utilizando campos das tabelas referenciadas) deverão ser combinadas a WHEN NOT MATCHED e WHEN MATCHED, possibilitando assim estipular diferentes condições e suas respectivas ações;
  • Concluindo, o bloco iniciado pela palavra reservada MERGE deve ser finalizado por ponto-e-vírgula (“;”).
MERGE dbo.Produtos P
USING dbo.TMP_Produtos TMP
    ON TMP.CodigoBarras = P.CodigoBarras
WHEN NOT MATCHED THEN
    INSERT (CodigoBarras, NomeProduto, Categoria, PrecoVenda, DataInclusao)
    VALUES (TMP.CodigoBarras, TMP.NomeProduto, TMP.Categoria, TMP.PrecoVenda, GETDATE())
WHEN MATCHED THEN
    UPDATE SET
        NomeProduto = TMP.NomeProduto,
        Categoria = TMP.Categoria,
        PrecoVenda = TMP.PrecoVenda,
        DataAtualizacao = GETDATE()
;

Listagem 5: Utilizando o comando MERGE

A execução de uma nova consulta produzirá um resultado semelhante ao que consta na Imagem 2 (alterações estão assinaladas em vermelho, ao passo que inclusões em azul).

Imagem02

Imagem 2. Resultado de uma consulta à tabela Produtos após a execução do comando MERGE

Chego aqui ao final de mais um artigo. Neste novo post procurei demonstrar como o comando MERGE pode simplificar o trabalho dos desenvolvedores, permitindo mesclar num único bloco de código instruções de INSERT, UPDATE e DELETE.

Espero que este conteúdo possa ser útil no seu dia-a-dia. Até uma próxima oportunidade!

Links

MERGE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/bb510625.aspx

Renato Groffe

Atua como consultor em atividades voltadas ao desenvolvimento de softwares há mais de 13 anos. Bacharel em Sistemas de Informação, com especialização em Engenharia de Software. Microsoft Certified Technology Specialist (Web, WCF, Distributed Applications, ADO.NET, Windows Forms), Microsoft Specialist (HTML5 with JavaScript and CSS3, Developing ASP.NET MVC 4 Web Applications), Oracle Certified Associate (PL/SQL), Sun Certified (SCJP, SCWCD), ITIL Foundation V2, Cobit 4.1 Foundation.

Facebook Google+ 

Comentários

comentarios