none
IDENTITY_INSERT not working

    Question


  • This is a very strange problem:

    If I run the following consecutively, I will get the following results.

    SET IDENTITY_INSERT table1 ON
    >>Command(s) completed successfully.

    INSERT table( ID, Title) VALUES (1001, 'Temp Title')
    >>Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table 'table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

    SET IDENTITY_INSERT table2 ON
    >>Msg 8107, Level 16, State 1, Line 1
    IDENTITY_INSERT is already ON for table 'server.dbo.table1'. Cannot perform SET operation for table 'table2'.

    I'm hoping somebody could direct mo to what error in particular I am encountering. I can see that I have successfully switched on the IDENTITY_INSERT() function but I STILL could not insert the ID to table1.
    Tuesday, October 14, 2008 5:32 AM

Answers

  • Hi:

     

       Give you a simple example:

     

    Code Snippet

    use tempdb
    go

    create table t1(id int identity(1,1),name char(1))
    go

    set identity_insert t1 on
    go

    insert t1(id,name) values(2,'a')
    go

    select * from t1
    go


    set identity_insert t1 off
    go

    drop table t1
    go

     

     

    Hope it helps.
    Tuesday, October 14, 2008 5:47 AM
  • I have no idea WHY that worked.

    The only difference between your code and mine is that you have 'go' and you explicitly listed down the attribute names and you had a select so I tried that (yeah, I try all the suggestions before deciding if it helped or not)

    Thank you Smile

    ANSWER:
    If all else fails with IDENTITY_INSERT
    - use 'go' every after command
    - select after the 'insert'
    - explicitly list down all attribute names even though you are inserting in the right order
    Tuesday, October 14, 2008 5:58 AM

All replies

  •  

    Ooops.

    I think i din't read it properly

    Tuesday, October 14, 2008 5:38 AM
  • I'm sorry but I would just have to say, "LIKE DUUUH"

    As you can see in my code, I explicitly called IDENTITY_INSERT which is where the whole problem revolves.

    I know that UNLESS I call that, I wouldn't be able to insert Identity values.

    Please refer to the first post more closely.
    Tuesday, October 14, 2008 5:46 AM
  • Hi:

     

       Give you a simple example:

     

    Code Snippet

    use tempdb
    go

    create table t1(id int identity(1,1),name char(1))
    go

    set identity_insert t1 on
    go

    insert t1(id,name) values(2,'a')
    go

    select * from t1
    go


    set identity_insert t1 off
    go

    drop table t1
    go

     

     

    Hope it helps.
    Tuesday, October 14, 2008 5:47 AM
  • I have no idea WHY that worked.

    The only difference between your code and mine is that you have 'go' and you explicitly listed down the attribute names and you had a select so I tried that (yeah, I try all the suggestions before deciding if it helped or not)

    Thank you Smile

    ANSWER:
    If all else fails with IDENTITY_INSERT
    - use 'go' every after command
    - select after the 'insert'
    - explicitly list down all attribute names even though you are inserting in the right order
    Tuesday, October 14, 2008 5:58 AM