Dicas T-SQL – Como recuperar os dados de uma tabela utilizando o IDENTITY_INSERT?

Olá humanos,

Nesse post vou mostrar como o comando IDENTITY_INSERT pode ser útil para recuperar os dados de uma tabela. #gogogo


IDENTITY_INSERT:

O comando IDENTITY_INSERT permite inserir valores de forma explícita em uma coluna que possui um IDENTITY.

Segue abaixo uma referência da documentação da Microsoft com mais detalhes:

https://docs.microsoft.com/pt-br/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver16

“Como assim Luiz? Não entendi nada!”

Calma que agora vamos ver um exemplo para entender melhor OK.


Gerando o Cenário do Problema:

Você deve conhecer alguém que já fez um UPDATE ou DELETE sem WHERE, ou até mesmo você já deve ter feito (se não fez ainda pode esperar que o seu dia vai chegar kkk).

Deixo aqui também uma referência para um post muito TOP do Gustavo Larocca (lindoooo S2) sobre essa situação:

https://gustavolarocca.com.br/update-sem-where-quem-nunca/

Uma possível solução seria você restaurar os backups de log até algum momento anterior (o mais próximo possível) ao comando que gerou o problema e depois recuperar os dados da tabela (a opção STOPAT pode ser muito útil nesse caso).

https://luizlima.net/script-restore-restaurando-varios-arquivos-de-backup-de-log-opcoes-stopat-e-standby/

Em resumo, o nosso problema será um DELETE SEM WHERE e depois iremos recuperar os dados e utilizar o comando IDENTITY_INSERT para inserir os dados novamente na tabela original. Agora chega de enrolação e vamos colocar a mão na massa!


Utilizando o IDENTITY_INSERT na prática:

Vamos criar uma tabela chamada “Vendas” com a coluna “Id_Venda” como IDENTITY e inserir alguns registros. Repare que no INSERT não vamos informar os valores dessa coluna, pois eles são gerados automaticamente pelo SQL Server.

Se você selecionar o nome da tabela no código e apertar ALT + F1 irá retornar a definição dela. Repare que a coluna “Id_Venda” é um IDENTITY.

Para o post não ficar muito grande, vou criar uma tabela chamada “Vendas_Backup” com todos os dados da tabela “Vendas”. Depois vamos utilizar essa tabela para recuperar os dados.

Agora vamos gerar o problema fazendo o famoso DELETE SEM WHERE!!! Feito isso, nossa tabela de Vendas ficou VAZIA!! #medo

“E agora Luiz, o que eu faço para recuperar os dados???”

Agora vamos tentar recuperar os dados utilizando a tabela “Vendas_Backup”.

Msg 544, Level 16, State 1, Line 34

Cannot insert explicit value for identity column in table ‘Vendas’ when IDENTITY_INSERT is set to OFF.

Como podemos perceber, ao utilizar um INSERT simples não é possível inserir valores explícitos em uma coluna IDENTITY, pois ela já gera os valores de forma automática para facilitar a nossa vida.

Contudo, podemos utilizar o comando IDENTITY_INSERT para contornar essa restrição e por fim ter os dados novamente na tabela Vendas! TOP hein!!!


Observações:

Vou deixar aqui também mais duas dicas que já me ajudaram muito no passado e que me geraram muitas dúvidas:

  • Um detalhe MUITO IMPORTANTE e que já quebrei a cabeça com isso no passado é: ao utilizar o IDENTITY_INSERT você deve obrigatoriamente especificar a lista com o nome das colunas no INSERT, caso contrário você pode ver o erro abaixo:
Msg 8101, Level 16, State 1, Line 63

An explicit value for the identity column in table ‘dbo.Vendas’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Ou seja, para resolver basta especificar o nome das colunas no INSERT INTO.

  • Por fim, a última dica é: se o valor inserido for maior que o valor do identity atual da tabela, o SQL Server usará automaticamente o novo valor inserido como o valor do identity atual.

Aqui vamos utilizar a função IDENT_CURRENT que retorna o valor do último IDENTITY gerado na tabela.

Nesse TESTE 1 vou apenas inserir os mesmos IDs anteriores. Com isso, não inserimos nenhum valor maior do que o IDENTITY atual da tabela e segue a vida normalmente.

Já no TESTE 2, vou inserir uma linha com o valor 100. Como esse valor é MAIOR que o IDENTITY atual da tabela, o SQL Server irá atualizar automaticamente o valor do identity para garantir que os próximos IDs sejam maiores do que 100.

Se o SQL Server não fizesse isso, o identity iria continuar 5 e ao ir inserindo os próximos dados, quando chegasse no ID 100 poderíamos ter um ERRO.

Depois disso ficamos com apenas um registro com o ID 100 na tabela.

Por fim, vamos inserir mais uma linha apenas para mostrar que o próximo ID gerado automaticamente será o 101!


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

2 comentários em “Dicas T-SQL – Como recuperar os dados de uma tabela utilizando o IDENTITY_INSERT?

Deixe uma resposta