locked
Cannot insert explicit value for identity column in table '#Temp when IDENTITY_INSERT is set to OFF RRS feed

  • Question

  • I am selecting into a temp table to create it and then inserting into it as i validate info, I have never got this msg before it looks like Identity_Insert is causing the issue what do i do? My code is below

    Msg 544, Level 16, State 1, Line 43
    Cannot insert explicit value for identity column in table '#Temp

    SELECT   U.Row_Id     AS RecordNumber
            ,1         AS errorCode
    INTO    #Temp
    FROM  TBL_Unknown U


    INSERT INTO    #Temp(RecordNumber,errorCode)             
        SELECT U.Row_Id    AS RecordNumber
            ,2        AS errorCode
        FROM  TBL_Unknown U
    McC
    Tuesday, November 17, 2009 7:57 PM

Answers

  • Did you try

    SET IDENTITY_INSERT #temp ON
    INSERT INTO    #Temp(RecordNumber,errorCode)             
        SELECT U.Row_Id    AS RecordNumber
            ,2        AS errorCode
        FROM  TBL_Unknown U

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, November 17, 2009 8:03 PM

All replies

  • Did you try

    SET IDENTITY_INSERT #temp ON
    INSERT INTO    #Temp(RecordNumber,errorCode)             
        SELECT U.Row_Id    AS RecordNumber
            ,2        AS errorCode
        FROM  TBL_Unknown U

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, November 17, 2009 8:03 PM
  • If IDENTITY_INSERT is OFF, you can't explicity insert into an identity column.  IDENTITY_INSERT needs to be ON for that to work, and Naomi below has an example.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Tuesday, November 17, 2009 8:22 PM
  • You rock Naom, Thanks for all the help!

    McC
    Tuesday, November 17, 2009 8:24 PM