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.

USE Traces
GO

CREATE TABLE Vendas (
	Id_Venda INT IDENTITY(1,1),
	Vl_Venda NUMERIC (9,2),
	Dt_Venda DATETIME
)

INSERT INTO Vendas (Vl_Venda, Dt_Venda)
VALUES	(1000.00, '2022-01-01'),
		(500.00, '2022-02-01'),
		(2400.00, '2022-03-01'),
		(8300.00, '2022-04-01'),
		(250.00, '2022-05-01')

SELECT * FROM Vendas
GO

USE Traces

CREATE TABLE Vendas (

Id_Venda INT IDENTITY(1,1),

Vl_Venda NUMERIC (9,2),

Dt_Venda DATETIME

)

INSERT INTO Vendas (Vl_Venda, Dt_Venda)

VALUES (1000.00, '2022-01-01'),

(500.00, '2022-02-01'),

(2400.00, '2022-03-01'),

(8300.00, '2022-04-01'),

(250.00, '2022-05-01')

SELECT * FROM Vendas

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.

SELECT * 
INTO Vendas_Backup
FROM Vendas

SELECT *

INTO Vendas_Backup

FROM Vendas

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

DELETE FROM Vendas

SELECT * FROM Vendas

DELETE FROM Vendas

SELECT * FROM Vendas

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

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

INSERT INTO Vendas(Id_Venda, Vl_Venda, Dt_Venda)
SELECT Id_Venda, Vl_Venda, Dt_Venda 
FROM Vendas_Backup

INSERT INTO Vendas(Id_Venda, Vl_Venda, Dt_Venda)

SELECT Id_Venda, Vl_Venda, Dt_Venda

FROM 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!!!

SET IDENTITY_INSERT dbo.Vendas ON;  

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)
SELECT Id_Venda, Vl_Venda, Dt_Venda 
FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

SELECT * FROM dbo.Vendas

SET IDENTITY_INSERT dbo.Vendas ON;

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)

SELECT Id_Venda, Vl_Venda, Dt_Venda

FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

SELECT * FROM dbo.Vendas

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:

DELETE FROM dbo.Vendas
GO

SET IDENTITY_INSERT dbo.Vendas ON;  

INSERT INTO dbo.Vendas
SELECT Id_Venda, Vl_Venda, Dt_Venda 
FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

DELETE FROM dbo.Vendas

SET IDENTITY_INSERT dbo.Vendas ON;

INSERT INTO dbo.Vendas

SELECT Id_Venda, Vl_Venda, Dt_Venda

FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

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.

SET IDENTITY_INSERT dbo.Vendas ON;  

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)
SELECT Id_Venda, Vl_Venda, Dt_Venda 
FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

SET IDENTITY_INSERT dbo.Vendas ON;

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)

SELECT Id_Venda, Vl_Venda, Dt_Venda

FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

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.

-- TESTE 1:
DELETE FROM dbo.Vendas

SELECT IDENT_CURRENT('dbo.Vendas')

SET IDENTITY_INSERT dbo.Vendas ON;  

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)
SELECT Id_Venda, Vl_Venda, Dt_Venda 
FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

SELECT IDENT_CURRENT('dbo.Vendas')

-- TESTE 1:

DELETE FROM dbo.Vendas

SELECT IDENT_CURRENT('dbo.Vendas')

SET IDENTITY_INSERT dbo.Vendas ON;

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)

SELECT Id_Venda, Vl_Venda, Dt_Venda

FROM Vendas_Backup

SET IDENTITY_INSERT dbo.Vendas OFF;

SELECT IDENT_CURRENT('dbo.Vendas')

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.

-- TESTE 2:
DELETE FROM dbo.Vendas

SELECT IDENT_CURRENT('dbo.Vendas')

SET IDENTITY_INSERT dbo.Vendas ON;  

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)
VALUES (100, 1000.00, '20220525')

SET IDENTITY_INSERT dbo.Vendas OFF;

SELECT IDENT_CURRENT('dbo.Vendas')

-- TESTE 2:

DELETE FROM dbo.Vendas

SELECT IDENT_CURRENT('dbo.Vendas')

SET IDENTITY_INSERT dbo.Vendas ON;

INSERT INTO dbo.Vendas(Id_Venda, Vl_Venda, Dt_Venda)

VALUES (100, 1000.00, '20220525')

SET IDENTITY_INSERT dbo.Vendas OFF;

SELECT IDENT_CURRENT('dbo.Vendas')

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

SELECT * FROM dbo.Vendas

1	SELECT * FROM dbo.Vendas

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

INSERT INTO dbo.Vendas(Vl_Venda, Dt_Venda)
VALUES (5000.00, '20220601')

SELECT * FROM dbo.Vendas

INSERT INTO dbo.Vendas(Vl_Venda, Dt_Venda)

VALUES (5000.00, '20220601')

SELECT * FROM dbo.Vendas

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?”

Anderson Aroucha 26 de maio de 2022 Responder

Sempre com conteúdos sensacionais.
Muito bom!!!!
- Luiz Lima Autor do post26 de maio de 2022 Responder
  
  Muito obrigado gato! Que bom que gostou!
  
  Abraço,
  Luiz Vitor

IDENTITY_INSERT:

Gerando o Cenário do Problema:

Utilizando o IDENTITY_INSERT na prática:

Observações:

Compartilhe isso:

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

Deixe uma respostaCancelar resposta