DTS Violation of Primary Key

Question

Post reply

DTS Violation of Primary Key

lara31

SSCommitted

Points: 1802
More actions
March 28, 2005 at 7:30 am

#163999

In my dts package i have a t-sql task step that does the following.

Insert New Record
INSERT INTO MSR_Incent
        (MSR_Incent.Teller,
MSR_Incent.Branch,
   MSR_Incent.Acct,
              MSR_Incent.sfx,
MSR_Incent.DOS,
              MSR_Incent.Base_Serv,
MSR_Incent.Has1,
              MSR_Incent.Has2,
MSR_Incent.Has3,
MSR_Incent.Has4,
MSR_Incent.Has5,
   MSR_Incent.Has1Da,
MSR_Incent.Has2Da,
MSR_Incent.Has3Da,
MSR_Incent.Has4Da,
MSR_Incent.Has5Da)
SELECT MSR_Incent_I.Teller,
MSR_Incent_I.Branch,
  MSR_Incent_I.Acct,
       MSR_Incent_I.sfx,
MSR_Incent_I.DOS,
       MSR_Incent_I.Base_Serv,
Msr_Incent_I.Has1,
       Msr_Incent_I.Has2,
Msr_Incent_I.Has3,
Msr_Incent_I.Has4,
Msr_Incent_I.Has5,
             Msr_Incent_I.Has1Da,
Msr_Incent_I.Has2Da,
Msr_Incent_I.Has3Da,
Msr_Incent_I.Has4Da,
Msr_Incent_I.Has5Da
FROM Msr_Incent_I
WHERE not EXISTS
    (SELECT *
       FROM Msr_Incent
       WHERE MSR_Incent_I.Teller = MSR_Incent.Teller and
                    MSR_Incent_I.Branch = MSR_Incent.Branch and
                    MSR_Incent_I.Acct = MSR_Incent.Acct and
                    MSR_Incent_I.Sfx = MSR_Incent.Sfx and
                    MSR_Incent_I.DOS = MSR_Incent.DOS and
                    MSR_Incent_I.Base_Serv = MSR_Incent.Base_Serv
     )
go

when i try to run it i get a the following error:
Violation of the PRIMARY KEY constraint 'Pk_MSR_incent'. Cannot Insert duplicate key in Object'MSR_Incent'
i have checked the two tables and i cannot figure out what the problem is. the step ran in the past with out any errors.

any ideas?
[font="Comic Sans MS"][/font]It is what it is.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

under your not EXISTS clause, are you sure all the join conditions cover the primary key on the main table?

Shamless self promotion - read my blog http://sirsql.net

lara31 SSCommitted Points: 1802 More actions · Answer 2

yes it does.

[font="Comic Sans MS"][/font]It is what it is.

philcart SSC-Forever Points: 47794 More actions · Answer 3

Try using a LEFT JOIN instead.

blah...
blah...
 Msr_Incent_I.Has5Da
  FROM Msr_Incent_I
  LEFT JOIN Msr_Incent
       ON MSR_Incent_I.Teller = MSR_Incent.Teller 
       AND MSR_Incent_I.Branch = MSR_Incent.Branch 
       AND MSR_Incent_I.Acct = MSR_Incent.Acct 
       AND MSR_Incent_I.Sfx = MSR_Incent.Sfx 
       AND MSR_Incent_I.DOS = MSR_Incent.DOS 
       AND MSR_Incent_I.Base_Serv = MSR_Incent.Base_Serv 
WHERE MSR_Incent.Teller IS NULL
go

--------------------
Colt 45 - the original point and click interface

lara31 SSCommitted Points: 1802 More actions · Answer 4

nope. did not work, still getting thesame error

[font="Comic Sans MS"][/font]It is what it is.

philcart SSC-Forever Points: 47794 More actions · Answer 5

In that case the fields MSR_Incent.Teller, MSR_Incent.Branch, MSR_Incent.Acct, MSR_Incent.Sfx, MSR_Incent.DOS & MSR_Incent.Base_Serv do not make up a unique key.

You'll need to specify all necessary fields that make up the unique key.

--------------------
Colt 45 - the original point and click interface

lara31 SSCommitted Points: 1802 More actions · Answer 6

Thanks will try that also, but what i did as a temp fis was to drop the msr_incent table and rebuild it, and for some reason the dts worked

[font="Comic Sans MS"][/font]It is what it is.

Agson Chellakudam Ten Centuries Points: 1387 More actions · Answer 7

Hi

I have faced the similar problem..But in that case 'select query was returning duplicate rows.

So I just given group by of all selected fields and the issue was solved

Agson C A