Help!!! Getting Violation of PRIMARY KEY on insert

[cjblake]

I just archived and deleted a bunch of records and there was a very important piece of my code commented out that caused me to delete too many records. Fortunately I archived them, but I'm trying to insert them back into the table, but I get this message:
Violation of PRIMARY KEY constraint 'PK__gmcmpany__1CBC4616'. Cannot insert duplicate key in object 'tablename'.

Here's the SQL statement that I'm using for the insert - note that I'm verifying that the records don't exist in that table:

INSERT INTO tbldeletedfrom (field list)
select (values)
from tablea a, archivetable b
where a.id = b.id and not exists
(select c.id from tbldeletedfrom c where c.id = b.id)

What might be going on? I need help asap. Thanks!

 

[bikerboy718]

What is table A? And do you need it when reinserting your records. If archivetable contains the exact columns as tbldeletefrom then why is table a included in the insert statement? I do not believe that you need to reference table A.

You could probably do this instead

INSERT INTO tbldeletedfrom (field list)
select (values)
from archivetable b 
where b.id not in 
(select c.id from tbldeletedfrom)

With Great Power Comes Great Responsibility!!!

Michael

 

[cjblake]

Thanks so much for your reply. I need tablea because that tells me which of the deleted records to insert back into the original table. I don't want to insert them all back in.

Any thoughts on why I'm getting the PK violation when I'm verifying that the records don't exist?

Thanks!

 

[bikerboy718]

have you tried to break up the select statements to see what each one is returning.

select (values)
from tablea a, archivetable b 
where a.id = b.id

select c.id 
from tbldeletedfrom c, archivetable b 
where c.id = b.id)

IF you insert them into memory tables and then do a compare on the two tables then you can see for sure if a duplicate record exist between the two statements.




With Great Power Comes Great Responsibility!!!

Michael

 

[Catadmin]

Is your Primary Key an Identity column?

If so, try setting Identity_Insert ON for the table you are inserting the records back into.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[cjblake]

The PK is not an identity column, but thanks for the suggestion!

 

[Catadmin]

Just had another thought. Is this Primary Key a combined PK?

I have lots of problems with this when trying to re-insert records and it usually turns out my SELECT statement is doubling up the records (or tripling) due to the way I do joins.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[bikerboy718]

Sorry about that CJ. I was under the impression that your primary key was an identity column. In your database is it possible to reuse a primary key that has been archived? Once a record has been sent to the archivetable can the primary key key for that record be reused in the tbldeletedfrom table?

I would break up what you are trying to do in select statements and compare. That way you can see exactly were the problem is.

With Great Power Comes Great Responsibility!!!

Michael

 

[cjblake]

I figured out my problem. Because I'm using tablea to help me determine which records needed to be inserted, I was getting multiple records for the id, thus causing the duplicate error. What I needed to do was add the DISTINCT keyword in my select statement to avoid this problem.

Thanks for any time you spent looking into this! I knew it would make sense in the end...

Now I can rest this weekend!!!

 

[Catadmin]

Glad we could help.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"