Cláusulas OUTPUT e INTO
A cláusula OUTPUT é uma parte incrível do SQL Server que poucas pessoas conhecem. Ela é uma parte da consulta que retorna os dados de antes ou depois da operação executada. Esta opção é bastante útil para validar os dados tratados. Podemos usar OUTPUT com os comandos INSERT, UPDATE, DELETE e MERGE.
Para acessar os dados que estão sendo tratados na instrução SQL, usamos os prefixos de coluna INSERTED e DELETED. Familiar? Claro! São as “tabelas” que tratamos em triggers para manipular os dados na manutenção de uma tabela. Cada comando tem seu prefixo de coluna disponível:
INSERT: inserted (para os novos dados)
UPDATE: inserted (para os novos dados) e deleted (para os dados antes da alteração)
DELETE: deleted (para os dados que serão apagados)
MERGE: inserted (para os novos dados) e deleted (para os dados antes da alteração)
Para facilitar a demonstração, vamos criar uma tabela de produtos:
Veja o resultado do script:
Na execução do comando INSERT, a cláusula OUTPUT exibe os valores que foram incluídos na tabela. Ou seja, o conteúdo armazenado na “tabela” inserted.
O mesmo conceito se aplica ao comando UPDATE. Observe que a coluna inserted.Descricao é exibida com um “alias”, o que ajuda a identificar as informações no retorno do comando:
No comando DELETE:
E no comando MERGE:
Observe que, embora o comando MERGE execute duas ações (INSERT e UPDATE), a cláusula OUTPUT é uma só.
Se a cláusula OUTPUT já é pouco conhecida, seu uso combinado com a cláusula INTO é menos conhecido ainda! E é nessa combinação que está o grande poder nesses comandos.
Digamos que você inseriu dados em uma tabela e precisa saber os valores de uma coluna IDENTITY para usar em outra parte do código... Ou então quer gerar um log com as alterações efetuadas na tabela... Pois é! A combinação de OUTPUT e INTO nos dá essa informação!
IMPORTANTE: Os dados disponibilizados pela cláusula OUTPUT devem ir para uma tabela ou uma variável tabela.
Vamos rever os exemplos acima, incluindo a cláusula INTO, começando do zero.
O que aconteceu aqui?! Vamos analisar comando a comando:
Na linha 1, a tabela temporária #Produto foi apagada.
Na linha 3, a tabela temporária #Produto foi criada.
Nas linhas 8 a 11, temos a criação de uma variável do tipo Table... Com a mesma estrutura de #Produto.
Nas linhas 14 a 19, executamos o comando INSERT, que incluiu 3 linhas na tabela #Produto.
Na linha 22, exibimos o conteúdo da tabela temporária #Produto.
Na linha 23, exibimos o conteúdo da variável table @SaidaProduto.
Peraí! A variável table @SaidaProduto tem 3 registros, iguais à tabela #Produto. Como? Será que faltou exibir o INSERT no script?
Esse é o resultado das cláusulas OUTPUT e INTO no comando INSERT. Ao invés de exibir os valores como nos exemplos anteriores, eles foram armazenados na variável table.
Vamos complicar um pouco mais. Observe a criação da variável table @SaidaProduto. Agora, vamos guardar os valores antigos e os novos valores dos dados, além da data de geração do registro. Isso é bem semelhante a... Log?
A cláusula INTO é bem simples. Informamos o nome da variável table e a lista de colunas onde queremos armazenar o retorno da cláusula OUTPUT.
Não foi necessário incluir as colunas Codigo_Anterior, Descricao_Anterior e Preco_Anterior nas cláusulas OUTPUT e INTO, porque não temos esses valores no INSERT. E a coluna DtManutenção tem um valor default para preenche-la. Agora, para efetivar essas informações de LOG, basta incluir o conteúdo da variável table @SaidaProduto numa tabela de LOG. Um simples INSERT/SELECT resolve.
Vamos ver o comando UPDATE:
Interessante... Então minha variável table agora exibe o antes e depois do registro 1. Mas eu perdi os 3 registros do OUTPUT da inclusão dos produtos. Claro! Os comando foram executados separadamente, então a variável foi recriada e só guardou o conteúdo do último comando.
E se... Trocarmos a variável table por uma tabela “de verdade”?!
Vamos refazer todos os comandos, desde o início, com o INSERT e o UPDATE, mas ao invés de criar uma variável table, vamos usar uma tabela temporária...
Ah... Agora sim! A tabela #ProdutoLog armazenou todo o movimento da tabela #Produto: a inclusão das 3 linhas e a alteração da descrição e do preço do produto no código 1.
Vamos ver como fica o comando DELETE:
Para finalizar, vamos ver o comando MERGE:
Concluindo, com a combinação das cláusulas OUTPUT e INTO nos comandos de inclusão, alteração e exclusão, podemos:
Gerar log durante a manutenção da informação.
Guardar informações como o valor da chave primária de um registro, para utilizá-lo em outro comando.
Visualizar como o comando afetou os dados da tabela.