CODEWITHSUNDEEP
sqlsql-servert-sql
S..
S..

Reputation: 1272

Error when executing a stored Procedure

I am trying to execute a stored procedure which is failing while inserting the data from one table to another table. The error is

Cannot insert duplicate key row in object 'ExtpndPart1' with unique index 'IDX_Primary'

I know this error comes when the destination table has a primary key on a particular column and the source table has duplicates in that column. but i hv a condition which drops the primary key in the stored procedure.

IF EXISTS (SELECT * FROM dbo.sysindexes WHERE name  =  N'PK_ExtpndPart1' )
 ALTER TABLE [dbo].[ExtpndPart1] DROP [PK_ExtpndPart1]

Can any one help me??

Upvotes: 1

Views: 352

Answers (3)

Andriy M
Andriy M

Reputation: 77677

Either you or your server are clearly in the wrong. If you are right and there can be no attempts at inserting duplicates which would violate IDX_Primary, then it's your server's fault, obviously.

Otherwise you might just have overlooked the fact that the duplicates somehow are really there. Either they are in the source table, or the destination table is not being properly truncated.

It's all right that you are not willing to share confidential code, but then we have little information beside what you have revealed.

Anyway, I'm backing up what you have already been told: the offended index is IDX_Primary, not PK_ExtpndPart1. So what do you do? First, find the script that creates IDX_Primary and pay attention to the index expression/expressions. Having done that, just use the expression(s) in a simple query on your source table to find out if there are indeed no duplicates.

I think you could easily come up with some script of that sort, but just because I do not see how else I can help you in the lack of details I've decided to share with you this little template of mine:

SELECT
  ..., /* column(s) or expression(s) as used in IDX_Primary
         (actually, their counterparts in the source table) */
  COUNT(*)
FROM ... /* your source table */
GROUP BY ... /* the same IDX_Primary column(s) or expression(s) */
HAVING COUNT(*) > 1

As you can see, the result would be the very duplicates which are sure to upset IDX_Primary.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

Seems like you're looking for/dropping the wrong index. You're dropping PK_ExtpndPart1 but the error refers to IDX_Primary.

Upvotes: 1

Mark Wilkins
Mark Wilkins

Reputation: 41222

It looks like you are trying to drop index PK_ExtpndPart1, but the offending index is IDX_Primary.

Upvotes: 0

Related Questions