Link to home
Start Free TrialLog in
Avatar of IntercareSupport
IntercareSupport

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of IntercareSupport
IntercareSupport

ASKER

Perfect, and crazy fast.  I would have replied earlier, but I accidentally deleted a lot of code and had to recreate it.  

Thanks!