Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: identity insert set to off

    Hi all I am trying to insert data from an old table to a new one and for some reason its not working i keep getting an message that says
    Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table 'ParticipantActivity_tbl' when IDENTITY_INSERT is set to OFF.

    Can someone tell me what I am doing wrong??

    Code:
    INSERT INTO ParticipantActivity_tbl
                          ([Event Key field], [Activity Date], [Activity Location], [Event ID], [Event Name], [Primary Catagory], [Event Address], [Explain Other], Activity, [Event City], 
                          [To], [From], [Student ID], Instructor, [Accommodations made], [Covered during Session], Problems, StateServices, StateCategory, Services, 
                          [Other Comments])
    SELECT     [Event Key field], [Activity Date], [Activity Location], [Event ID], [Event Name], [Primary Catagory], [Event Address], [Explain Other], Activity, [Event City], 
                          [Beginning Time], [Ending Time], [Student ID], Instructor, [Accommodations made], [Covered during Session], Problems, [State Services], 
                          [State Category], Services, [Other Comments]
    FROM         [Student Activity]
    WHERE     (NOT EXISTS
                              (SELECT    [Event Key field], [Activity Date], [Activity Location], [Event ID], [Event Name], [Primary Catagory], [Event Address], [Explain Other], Activity, [Event City], 
                          [Beginning Time], [Ending Time], [Student ID], Instructor, [Accommodations made], [Covered during Session], Problems, [State Services], 
                          [State Category], Services, [Other Comments]
                                FROM         ParticipantActivity_tbl
                                WHERE      (ParticipantActivity_tbl.[Event Key field] = [Student Activity].[Event Key field])))

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SET IDENTITY_INSERT ParticipantActivity_tbl ON
    
    INSERT INTO ParticipantActivity_tbl  ....
    
    SET IDENTITY_INSERT ParticipantActivity_tbl OFF

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •