Can't insert into temp table (insert_identity issue?)

I am having trouble inserting records into a temp table that I've created.  When I execute the code below, it generates this error:

Cannot insert explicit value for identity column in table '#TEMP2______________________________________________________________________________________________________________000000003091' when IDENTITY_INSERT is set to OFF.

When I try to set the identity to "on" (SET IDENTITY_INSERT [#TEMP2] ON), it errors out this way:

IDENTITY_INSERT is already ON for table 'Patient_List.dbo.Unique_Users'. Cannot perform SET operation for table '#TEMP2'.

What am I doing wrong?
INSERT INTO [#TEMP2]
                      (mpl_id, Last_Name, First_Name, DOB, Addr1, Add2, City, ST, Zip)
SELECT     mpl_id, Last_Name, First_Name, DOB, Addr1, Add2, City, ST, Zip
FROM         [#TEMP1]
WHERE     (HP_Last_Visit =
                          (SELECT     MAX(HP_Last_Visit) AS HP_Last_Visit
                            FROM          [#TEMP1] AS l
                            WHERE      (Last_Name = [#TEMP1].Last_Name) AND (First_Name = [#TEMP1].First_Name) AND (DOB = [#TEMP1].DOB)))

Open in new window

IntercareSupportAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:


SET IDENTITY_INSERT Patient_List.dbo.Unique_Users OFF
SET IDENTITY_INSERT#temp2 ON
INSERT INTO [#TEMP2]
                      (mpl_id, Last_Name, First_Name, DOB, Addr1, Add2, City, ST, Zip)
SELECT     mpl_id, Last_Name, First_Name, DOB, Addr1, Add2, City, ST, Zip
FROM         [#TEMP1]
WHERE     (HP_Last_Visit =
                          (SELECT     MAX(HP_Last_Visit) AS HP_Last_Visit
                            FROM          [#TEMP1] AS l
                            WHERE      (Last_Name = [#TEMP1].Last_Name) AND (First_Name = [#TEMP1].First_Name) AND (DOB = [#TEMP1].DOB)))
0
 
IntercareSupportAuthor Commented:
Perfect, and crazy fast.  I would have replied earlier, but I accidentally deleted a lot of code and had to recreate it.  

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.