quarta-feira, 6 de abril de 2011

Auto-numeração em colunas (IDENTITY) - Parte 3

Para terminar a série de artigos sobre colunas auto-numeradas, seguem duas novas dicas: "Reiniciando uma coluna IDENTITY" e "Especificando o valor para a coluna IDENTITY ao inserir uma linha": 

Reiniciando uma coluna IDENTITY
É possível definir o valor da coluna IDENTITY a ser atribuído à próxima linha inserida na tabela. O comando DBCC CHECKIDENT realiza esta tarefa.

DBCC CHECKIDENT (yourtable, reseed, 34)

Importante: caso seja necessário configurar manualmente o próximo valor para a coluna IDENTITY pertencente a chave primária da tabela,  tome o cuidado de configurar um valor superior a todos os valores já inseridos para esta coluna, evitando conflitos com os registros já existentes. Para garantir que isto realmente acontecerá, pode-se utilizar o comando para encontrar o menor valor permitido para reiniciar o auto-incremento:

SELECT MAX(nome_campo) + 1 FROM tabela

Especificando o valor para a coluna IDENTITY ao inserir uma linha
Por padrão, não é possível definir o valor para uma coluna IDENTITY ao inserir ou atualizar um registro. Ao tentar realizar esta operação, o seguinte erro será lançado:

Msg 8101, Level 16, State 1, Line 7
An explicit value for the identity column in table [tabela] can only be specified when a column list is used and IDENTITY_INSERT is ON.

Veja o erro abaixo:


Entretanto, pode-se utilizar o comando SET para alterar a propriedade IDENTITY_INSERT da tabela desejada e desabilitar temporariamente a validação que causa o erro acima. Assim, os comandos para realizar esta tarefa teriam a seguinte estrutura:

SET IDENTITY_INSERT nome_tabela ON

   /* Comandos para inserir registros na tabela 
   determinando valores para a coluna IDENTITY  */
   INSERT  INTO nome_tabela (campo1,campo2,... ,campoN) 
        VALUES(valor1,valor2,... , valorN)

SET IDENTITY_INSERT nome_tabela OFF

Importante: Ao habilitar a inserção de valores para a coluna IDENTITY, é necessário explicitar o nome das colunas da tabela no comando INSERT.

No exemplo abaixo, a linha que causou erro no exemplo anterior é inserida com sucesso.


OBSERVAÇÃO: Ao inserir uma linha com um valor maior do que a contagem atual para a coluna IDENTITY, a numeração automática será automaticamente modificada para iniciar a partir do valor inserido.

Referências
DBCC CHECKIDENT (Transact-SQL)

3 comentários:

Alessandro H. Garbiati disse...

Muito bem explicado, muita gente se perde com SET IDENTITY_INSERT, esses dias mesmo estava explicando para uma colega, caso ainda tenha duvidas irei passar este post.

Edmar Carvalhaes disse...
Este comentário foi removido pelo autor.
Edmar Carvalhaes disse...

Ola tudo bem? Eu vi o seu artigo e o mesmo está muito bom e prático, mas estou com uma dúvida. Eu tenho uma tabela que tem o campo IDENTITY.
Acontece é que eu criei uma base vazia da base atual e quero replicar os dados desta tabela para a outra tabela de outro banco.
É a mesma tabela e mesma estrutura mas quanto eu faço isto:

SET IDENTITY_INSERT tabela ON

INSERT INTO tabela
SELECT * FROM banco..tabela

SET IDENTITY_INSERT tabela OFF

O sistema não deixa eu inserir os registros por causa do campo, a tabela é a mesma com mesma estrutura mas bancos diferentes. Estava vendo no GOOGLE que para eu usar o SET IDENTITY_INSERT tabela ON é para inserir registro por registro, não tem como eu inserir tudo de uma única vez conforme exemplo acima não?
Não tem como eu ficar expecificando os campos pois são várias tabelas e criei um cursor para varrer o banco todo para inserir os registros todos mas não executa.
Tem alguma sugestão?

Postar um comentário