Lição 2: Utilizando a cláusula OUTPUT e a instrução MERGE


Olá pessoal!

Na lição de hoje estaremos estudando a cláusula OUTPUT e a instrução MERGE, e para entendermos bem como ambas funcionam vejamos o que cada uma delas faz.

OUTPUT: Permite que você retorne informações de registros afetadas por instruções como INSERT, UPDATE, DELETE ou pela instrução MERGE, podendo retornar à aplicação como mensagem de confirmação, variáveis, em tabelas de destino ou views. Uma vez recuperada, estas informações podemos manipulá-las da maneira que nos for mais conveniente.

Abaixo vamos comentar sobre 3 cenários possíveis para a utilização da cláusula OUTPUT, porém faremos o exemplo de apenas 1 as outras 2 fica a cargo de vocês e aguardo feedback! J

· Cenário 1: Muitos ambientes precisam salvar os dados inseridos em uma tabela de auditoria, como exemplo uma empresa pode querer tirar relatóriospara saber quantas linhas foram inseridas em diferentes períodos de tempo.

· Cenário 2: Outra situação é o arquivamento de dados. Por exemplo, suponha que uma empresa queira mover todos os registros de pedidos feitos antes 01 de dezembro de 1997, à partir de uma tabela de Pedidos para a tabela PedidosArchive. Você pode excluir as linhas da tabela Pedidos e movê-los para a tabela PedidosArchive em uma única etapa usando DELETE com a cláusula OUTPUT,

· Cenário 3: Neste último a idéia é de a empresa poder saber qual o status de antes e depois de uma alteração em uma determinada coluna. Vejamos o exemplo abaixo onde iremos alterar o nome de uma determinada categoria, para isso utilizaremos a base de dados Northwind, a tabela já existente Categories e uma segunda tabela que criamos chamada CategoriasModificadas que possui as colunas CategoryID, CategoryNameDeletado, CategoryNameNovo, DataCriacao e Usuario.

o Na tabela abaixo apresentamos os dados originais.

output00

o Com o código abaixo iremos alterar o dado da coluna CategoryName que possue o CategoryID 1 e popular nossa nova tabela CategoriasModificadas com os dados alterados e recupoerados.

1: UPDATE Categories

2: SET CategoryName = ‘Bebidas’

3: OUTPUT inserted.CategoryID, deleted.CategoryName

4: , inserted.CategoryName, getdate(), SUSER_SNAME()

5: INTO CategoriasModificadas

6: WHERE CategoryID = 1;

o Com isso teremos os seguintes resultados respectivamente nas tabelas Categories e CategoriasModificadas

output01

output02

MERGE: Nos permite executar comandos como INSERT, UPDATE ou DELETE baseando-se em duas tabelas sendo uma de origem e outra de destino.

No exemplo abaixo iremos comparar os dados de duas tabelas e quando um destes dados não estiverem na tabela de origem, o valor da coluna Cancelled na tabela de destino será alterado para True.

· Neste primeiro momento exibimos os dados iniciais da tabela de origem, observe que ainda temos os registros com o OrderID 10248.

merge00

· Neste instante eu excluí todos os registros que possuíam o OrderID = 10248 e executei a seguinte instrução:

1: MERGE INTO [Historico Order Details] AS ODH

2: USING [order details] AS OD

3: ON ODH.orderid = OD.orderid

4: AND ODH.ProductID = OD.ProductID

5: WHEN NOT MATCHED BY TARGET THEN

6: INSERT (OrderID, ProductID, UnitPrice, Quantity, Discount, ModifiedDate, Cancelled)

7: VALUES (OrderID, ProductID, UnitPrice, Quantity, Discount, ModifiedDate, ‘False’)

8: WHEN NOT MATCHED BY SOURCE THEN

9: UPDATE SET ODH.Cancelled = ‘True’;

· Sendo assim teremos os seguintes resultados em nossas tabelas:

o Na tabela de origem os registros com OrderID = 10248 não mais existem.

merge00a

o Já na tabela de destino os registros com OrderID = 10248 além de estarem presentes a coluna Cancelled foi alterada para True pois estes dados já existiam na tabela de destino e quando executamos a instrução acima atendeu nossa condição de que todos os dados que não estiverem na tabela de origem tivesse os dados da coluna Cancelled alterado para True.

merge01

Espero que tenham entendido, qualquer dúvida fico à disposição.

Grande abraço e sucesso a todos.

Lucas A. Romão
MSN:
laromao@oxygenhost.com.br
Blog: http://laromao.wordpress.com/ | Twitter: @LucasRomao
Líder da Comunidade Azure Services Brasil | @AzureServicesBR

Anúncios

One Response to Lição 2: Utilizando a cláusula OUTPUT e a instrução MERGE

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: