Gostaria de comentar uma dica simples e rápida para SQL Server, que frequentemente sou questionado a respeito em sala de aula. Eventualmente é necessário "forçar" o valor de uma coluna Identity durante a inserção de uma linha em uma tabela com colunas definidas como Identity.
Só lembrando... Quando uma coluna é definida com o atributo identity, significa que o SQL Server se encarregará de informar este valor automaticamente no momento em que a linha for criada na tabela. Deste modo, este valor não pode ser informado pelo comando Insert.
O comando Identity_Insert
A solução é bem simples. Quando você precisar inserir alguma linha e forçar o valor da coluna Identity, utilize os comandos:
Antes de inserir, execute o comando:
SET IDENTITY_INSERT nome_da_tabela ON
Depois de inserido, retorne o autoidentity novamente:
SET IDENTITY_INSERT nome_da_tabela OFF
Um exemplo prático
Copie todo o conteúdo do código abaixo, no Query Analyzer e execute instrução por instrução para entender o funcionamento. Para evitar a necessidade de criar uma nova tabela em algum banco de dados, note que fiz uso de uma tabela temporária para o exemplo.
Exemplo
-- O simbolo # antes do nome da tabela, indica que ela é uma
-- Tabela Temporária
create table #testing
(
ID int not null IDENTITY, -- Coluna do tipo Identity
nome varchar(20) null
)
-- Insira algumas linhas apenas para teste:
-- Note que a coluna ID não são informadas, mesmo que Not Null.
-- Isso porque a diretiva IDENTITY se encarrega de preencher o valor
Insert #testing values ('Roberto')
Insert #testing values ('Lopes')
-- Veja os valores na tabela:
Select * from #testing
-- Tente inserir um valor de Identity:
Insert #testing (ID, nome) values (10, 'Tavares') -- Retorna Erro...
-- Execute o comando IDENTITY_INSERT ON:
Set IDENTITY_INSERT #testing ON
-- Agora tente inserir novamente:
Insert #testing (ID, nome) values (10, 'Tavares') -- Sem Erro...
-- Veja agora os valores na tabela (inclusive o último item):
Select * from #testing
-- Execute o comando IDENTITY_INSERT OFF:
Set IDENTITY_INSERT #testing OFF
-- Agora tente inserir novamente um valor na coluna ID:
Insert #testing (ID, nome) values (20, 'Giovanna') -- Retorna Erro...
-- Vamos excluir a tabela criada
drop table #testing
Conclusão
Este recurso é muito útil em determinadas situações (normalmente quando dados precisam ser corrigidos no banco de dados), porém não deve de maneira nenhuma ser utilizado em um processo de produção. Faça uso deste recurso como uma ferramenta de desenvolvimento.
Links
www.builddevelopers.net
Grupo de usuários .Net
msdn.microsoft.com
Biblioteca de referência Microsoft
