Violation of Primary Key...

I have a newly created table with no records in it. The structure of the table is Store_Ref_Novarchar
Section_Ref_Idint
Store_Section_Idvarchar
Section_Descvarchar
Registered_group_idchar
Date_Createddatetime
Created_Byvarchar
Statusvarchar

There is a primary key defined on
Store_Ref_Novarchar
Section_Ref_Idint
Store_Section_Idvarchar
Section_Descvarchar

I tried inserting this table from another table (#temp) with similar table structure with no primary keys and no constraints. It throwed an error "Violation of Primary key... cannot insert duplicate value into the table..."

What could be the problem???

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

No Frank. Data in my temp table is unique. any other clues???

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Additional info... I got this error in my development server. but when i tested that with sample data in my local machine, it ran fine.

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

quote:Originally posted by ramkumar.mu

No Frank. Data in my temp table is unique. any other clues???
Also while inserting i had a "where not exists" check condition to remove the duplicates

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

WHERE NOT EXISTS evaluates the rows before the insertion. It does not double-check during the insertion.

So you still have to eliminate duplicates from the source data.

"Where not exists" which i gave was just for standards. I already ensured that temp table had no duplicates with the below mentioned query...

SELECT Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc
from #temp
group by Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc
having count(*) > 1

The above query returned no results which brought me to a conclusion that the #temp table doesnt have any duplicates. correct me if i am wrong here...

quote:Originally posted by Adriaan

WHERE NOT EXISTS evaluates the rows before the insertion. It does not double-check during the insertion.

So you still have to eliminate duplicates from the source data.

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."

You have both Store_Section_Id and Section_Desc - are you sure the Section_Desc column is not redundant?

I would also compare the number of rows returned by ...

(1)
SELECT Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc from #temp

(2)
SELECT DISTINCT Store_Ref_No, Section_Ref_Id, Store_Section_Id, Section_Desc from #temp

If Section_Desc is indeed redundant, then run those two queries without Section_Desc.

Does your #temp table have a PK or a unique constraint on these 4 columns? Or is it on 5 columns, where the additional column is an IDENTITY column?

When i compared the rows from 1 and 2, the count was same.

My #temp table doesnt have any contraints. Is this a problem???

Thanks,
Ram

"It is easy to write code for a spec and walk in water, provided, both are freezed..."