Error from Insert Select statement

Database
Enthusiast

Error from Insert Select statement

Hi All

I am getting an error when running the following Insert Select statment

Statement

INSERT INTO  DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_NAME)

select  DISTINCT TRIM (CM_CUST_NAME)

FROM DDHWV03P.CM_CUSTOMER_STATIC_PERIODIC

Error

INSERT Failed. 2801:  Duplicate unique prime key error in DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO. 

Neither the BORROWER_NAME or CM_CUST_NAME fields are prime keys and both are the same datatype varchar(255)

Would I better using Update Select statement instead? Do I need to join the tables to do this

Any help on this would be very much appreciated

Regards

Donal

9 REPLIES
Enthusiast

Re: Error from Insert Select statement

Hi,

It means in the table FMP_STRATEGIC_CM_BORROWER_AUTO the colmn contains unique primary index please check. with the sql statement

show table <tablename>;

thanks,

Venkat

Senior Supporter

Re: Error from Insert Select statement

you said that BORROWER_NAME is not part of the PK, which you seem to have defined for the table.

In this case your statement will use null for all  DISTINCT TRIM (CM_CUST_NAME)

And if you have more then one you would have a duplicate PK...

So you would need to give the PK a unique not null value.

Enthusiast

Re: Error from Insert Select statement

Hi Guys

Thanks for all the help, I basically resoved the issue by dropping the table an recreating it with a PK and not a UPK

Donal

Senior Supporter

Re: Error from Insert Select statement

You might avoid the error message but I doubt that you resolved the issue. 

You will face skew issues if the Primary Index is Null for all rows.

Enthusiast

Re: Error from Insert Select statement

Thanks for the advice ulrich what would you recommend to totally resolve the issue?

Re: Error from Insert Select statement

Weird UPI violation 2801 issue while doing INSERT SELECT operation,

Source table is defined as UPPI with  4 columns and target table is defined same as UPPI with columns while performing INSERT SELECT I am getting UPI violation.

So when changed the target table with PPI( removing Unique) then it gets inserted, to my surprise I see duplicates based on the 4 columns(performed group by on 4 columns)  but the source is defined as as unique how can this be  . I am wondering . Can anyone answer this please

Teradata Employee

Re: Error from Insert Select statement

Different data types? 

Re: Error from Insert Select statement

I have even checked the data types .. same in both the tables

Teradata Employee

Re: Error from Insert Select statement

Could you please post your select statement... based on select we can judge how data is populated