Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-12-31
2
Medium Priority
?
211 Views
Last Modified: 2012-05-08
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

0
Comment
Question by:IntercareSupport
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26154816


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
 

Author Closing Comment

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

Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question