0

Definindo valores para uma coluna Identity

Posted by Fabio's Blog on 10:17

Frequentemente tenho que fazer carregamento de dados para fazer testes ou alguma alteração no sistema, e carregamos dados próximos os dados de produção, ai encontramos tabelas com coluna de IDENTITY e nos deparamos com o erro abaixo:


Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'TB_AGRI_CART_CLIE_STAT' when IDENTITY_INSERT is set to OFF.

Este erro ocorre por causa da coluna identity, não podemos especificar um valor para esta coluna, quem especifica é o próprio SQL, mas, para este caso podemos utilizar o comando IDENTITY_INSERT que "libera" a inclusão de dados para esta coluna. Segue abaixo:


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 ('Fábio')
Insert #testing values ('Luiz')

-- Veja os valores na tabela:
Select * from #testing

-- Tente inserir um valor de Identity:
Insert #testing (ID, nome) values (10, 'Moroni') -- Retorna Erro...

-- Execute o comando IDENTITY_INSERT ON:
Set IDENTITY_INSERT #testing ON

-- Agora tente inserir novamente:
Insert #testing (ID, nome) values (10, 'Moroni') -- 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, 'Moroni') -- 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.


Espero que tenha sido útil.

0

Formatação de Datas em SQL Server

Posted by Fabio's Blog on 14:45
Formatação de Datas em SQL Server.

Segue abaixo:

SELECT CONVERT(VARCHAR(12),GETDATE(),101) AS '101',
CONVERT(VARCHAR(12),GETDATE(),102) AS '102',
CONVERT(VARCHAR(12),GETDATE(),103) AS '103',
CONVERT(VARCHAR(12),GETDATE(),104) AS '104',
CONVERT(VARCHAR(12),GETDATE(),105) AS '105',
CONVERT(VARCHAR(12),GETDATE(),106) AS '106',
CONVERT(VARCHAR(12),GETDATE(),107) AS '107',
CONVERT(VARCHAR(12),GETDATE(),108) AS '108',
CONVERT(VARCHAR(12),GETDATE(),109) AS '109',
CONVERT(VARCHAR(12),GETDATE(),110) AS '110',
CONVERT(VARCHAR(12),GETDATE(),111) AS '111',
CONVERT(VARCHAR(12),GETDATE(),112) AS '112',
CONVERT(VARCHAR(12),GETDATE(),113) AS '113',
CONVERT(VARCHAR(12),GETDATE(),114) AS '114',
CONVERT(VARCHAR(12),GETDATE(),120) AS '120',
CONVERT(VARCHAR(12),GETDATE(),121) AS '121'

Espero que tenha sido útil.

Copyright © 2009 Fabio's Blog All rights reserved. Theme by Laptop Geek. | Bloggerized by FalconHive. Distribuído por Templates