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:
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:
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.
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
/* 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
3 comentários:
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.
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