none
SQL2005 Error "can only be specified when a column list is used and IDENTITY_INSERT is ON"

    Question

  • Hi all.

    Getting th efollowing error when running the statement below:

    Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table 'tblOSWP_IPDSLAM' can only be specified when a column list is used and IDENTITY_INSERT is ON.



    --SET IDENTITY_INSERT tblOSWP_IPDSLAM ON



    --SET IDENTITY_INSERT tblOSWP_IPDSLAM ON

     

    insert into tblOSWP_IPDSLAM
    SELECT oswpid, oswp FROM tblOSWP

    Both tables are built exactly the same and they both have only 2 fields.

    Thank you


    JCDS
    Monday, February 08, 2010 10:52 PM

Answers

  • Hi there,

    The message says "can only be specified when a column list is used and IDENTITY_INSERT is ON", you have set the IDENTITY_INSERT now you're only missing the column list. INSERT INTO tblOSWP_IPDSLAM (<column list>) SELECT ....

    José Cruz
    • Marked as answer by Maximusdm Monday, February 08, 2010 11:11 PM
    Monday, February 08, 2010 11:07 PM

All replies

  • This error occurs when user has attempted to insert a row containing a specific identity value into a table that contains an identity column.

    Run following commands according to your SQL Statement:

     

    SET IDENTITY_INSERT <tablename> ON

     

    YOUR INSERT STATEMENTS here

     

    SET IDENTITY_INSERT <tablename> OFF

     

    -- Do not comment any of them in the above code


    Thanks, Leks
    Monday, February 08, 2010 11:00 PM
    Answerer
  • Leks,

    I had run that command before and had the same error. Anyways, I ran it again and got the same error msg:

    SET

     

    IDENTITY_INSERT tblOSWP_IPDSLAM ON
    insert
    into tblOSWP_IPDSLAM
    SELECT oswpid, oswp FROM tblOSWP
    SET IDENTITY_INSERT tblOSWP_IPDSLAM OFF

    Thanks


    JCDS
    Monday, February 08, 2010 11:03 PM
  • Hi there,

    The message says "can only be specified when a column list is used and IDENTITY_INSERT is ON", you have set the IDENTITY_INSERT now you're only missing the column list. INSERT INTO tblOSWP_IPDSLAM (<column list>) SELECT ....

    José Cruz
    • Marked as answer by Maximusdm Monday, February 08, 2010 11:11 PM
    Monday, February 08, 2010 11:07 PM
  • that is it! thank you!

    Obrigado!
    JCDS
    Monday, February 08, 2010 11:11 PM