An explicit value for the identity column in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

An explicit value for the identity column in table

Author

Topic

thendraljazz
Starting Member

26 Posts

Posted - 2011-12-27 : 02:17:31

Hi

I have got this error while inserting into table.

An explicit value for the identity column in table 'firr' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This is my query

CREATE TABLE [dbo].[firr](
[fir_id] [int] IDENTITY(1,1) NOT NULL,
[cm_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_des] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_place] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dob] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[gender] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[age] [int] NULL,
[addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_time] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p_posting] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[location] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p_station_num] [int] NULL,
[fir_num] [int] NULL
) ON [PRIMARY]

How can i solve this?

Sachin.Nand

2937 Posts

Posted - 2011-12-27 : 03:42:03

What is the insert statement which you are using ?

PBUH

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-01-05 : 12:07:38

quote:
Originally posted by thendraljazz

Hi

I have got this error while inserting into table.

An explicit value for the identity column in table 'firr' can only be specified when a column list is used and IDENTITY_INSERT is ON.

This is my query

CREATE TABLE [dbo].[firr](
[fir_id] [int] IDENTITY(1,1) NOT NULL,
[cm_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_des] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_place] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dob] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[gender] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[age] [int] NULL,
[addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[c_time] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p_name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p_posting] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[location] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[p_station_num] [int] NULL,
[fir_num] [int] NULL
) ON [PRIMARY]

How can i solve this?

Use below statement before inserting into table where you want

SET IDENTITY_INSERT YourTableName ON

after inserting records n your table you can off this Set value
SET IDENTITY_INSERT YourTableName OFF

Go through below link
http://msdn.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

Kristen
Test

22859 Posts

Posted - 2012-01-05 : 12:13:25

quote:
Originally posted by vijays3

Use below statement before inserting into table where you want

SET IDENTITY_INSERT YourTableName ON

after inserting records n your table you can off this Set value
SET IDENTITY_INSERT YourTableName OFF

and, as per message "An explicit value for the identity column in table 'firr' can only be specified when a column list is used and IDENTITY_INSERT is ON.", you need an explicit column list too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources