Identity_Insert

• Confusing Queries

  Ten Centuries

  Points: 1137

  More actions

  January 11, 2012 at 9:40 pm

  #391292

  I created this table and I inserted 10 rows in it.

  CREATE table TEST (id INT identity(1,1) not null, name VARCHAR(30))

  Then after I tried to insert identity value manually using the code written below

  SET IDENTITY_INSERT TEST ON

  GO

  INSERT INTO TEST VALUES (11, 'Hrithik')

  But, it is giving me this error "Msg 8101, Level 16, State 1, Line 1

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

  I tried all the name convention with [database].[schema].

  Can anybody please help out?

  Thanks....

• JeffRush

  SSC Eights!

  Points: 855

  More actions

  January 11, 2012 at 10:15 pm

  #1432258

  You will need to specify your insert column list.

  Default behavior would not insert into the identity column.

  SET IDENTITY_INSERT TEST ON

  GO

  INSERT INTO TEST (id, name) VALUES (11, 'Hrithik')