Duplicate Unique Prime Key Error Informatica
Contents |
baseUsers turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for Search instead for Did you mean: Teradata : Product Forums : Database : Error from Insert Select statement Options Subscribe to failure 2801 duplicate unique prime key error RSS Feed Mark Topic as New Mark Topic as Read Float this Topic to duplicate unique prime key error in teradata the Top Bookmark Subscribe Printer Friendly Page donal28 Enthusiast Options Mark as New Bookmark Subscribe Subscribe to RSS Feed Get Direct Link
Duplicate Unique Prime Key Error In Target Table
Print Email to a Friend Report Inappropriate Content 06-12-2012 01:58 PM 06-12-2012 01:58 PM Error from Insert Select statement Hi All I am getting an error when running the following Insert Select statment Statement INSERT INTO
Duplicate Unique Key Error Wow
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 0 Kudos Reply All Forum Topics duplicate unique prime key error world of warcraft Previous Topic Next Topic 8 REPLIES venkylingutla Enthusiast Options Mark as New Bookmark Subscribe Subscribe to RSS Feed Get Direct Link Print Email to a Friend Report Inappropriate Content 06-12-2012 11:42 PM 06-12-2012 11:42 PM 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
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about
Duplicate Unique Primary Key Error In Informatica
hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask teradata odbc teradata driver teradata database duplicate unique prime key error Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join unique primary index in teradata them; it only takes a minute: Sign up Teradata identity column and “Duplicate unique prime key error in dbname.tablename” up vote 0 down vote favorite I created a table using the below definition for a Teradata identity column: ID http://community.teradata.com/t5/Database/Error-from-Insert-Select-statement/td-p/57936 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100000000 NO CYCLE), ---- UNIQUE PRIMARY INDEX ( ID ) For several months, the ID column has been working properly, automatically generating a unique value for the column. Over the past month, however, ELMAH has been intermittently reporting the following exception from our .NET 4.0 ASP.NET app: Teradata.Client.Provider.TdException: [Teradata Database] [2801] Duplicate unique prime key error in DATABASENAME.TABLENAME. I was able to replicate http://stackoverflow.com/questions/11727972/teradata-identity-column-and-duplicate-unique-prime-key-error-in-dbname-tablena it by opening SQL Assistant and inserting a bunch of records into the table with raw SQL. As expected, most of the time it would insert successfully, but other times it would throw the above exception. It appears that this error is occuring because Teradata is trying to generate a value for this column that it has previously generated. Does anyone have any idea how to get to the bottom of what's happening? At the very least, I'd like some way to debug the issue a bit deeper. unique identity teradata share|improve this question asked Jul 30 '12 at 19:06 oscilatingcretin 2,8261468131 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote I would suggest changing the definition of your identity column to GENERATED ALWAYS to prevent the application or ETL process from supplying a value that could have been used. In fact, it is recommended by Teradata that if you are using your IDENTITY column as part of a UPI that it should be defined as GENERATED ALWAYS ... NO CYCLE EDIT: If your business requirements are such that you must be able to provide a value I would also consider using a domain that is outside the range of values you have set aside for the IDENTITY column. You can use a negative domain or a range that is an order of magnitude beyond that of the IDENTITY
SQL JOINS , we sometime may face DUPLICATE PRIMARY KEY ERROR. This error may especially come while tables containing history are included in JOIN conditions. So while http://usefulfreetips.com/Teradata-SQL-Tutorial/remove-duplicate-unique-primary-key-error/ using SQL Joins, we must take care of this problem. Let us look into this problem in more detail: Duplicate Unique Primary Key Error in Teradata Suppose we have a table which is maintaining history of all the CUSTOMERS. And we do a join with this table with some other table on the basis of CUST_ID (key column in this case) . Since duplicate unique the table is maintaining history of CUSTOMERS, so it will be having multiple entries for same CUST_ID. So if the join condition is on CUST_ID, it will result in one to many mapping and the result set will also have multiple entries for same CUST_ID. Now if the target is having CUST_ID as primary key, it will throw UNIQUE PRIMARY KEY violation error. So duplicate unique prime how to avoid such situation ? The easiest way of overcoming this issue is to fetch the latest entry of CUST_ID from the history table. So in that case, it will be fetching only one instance of each CUST_ID even if multiple entries exists. Now we will see how we can implement this using the SQL in Teradata. If the table is maintaining history in the table then it must be having a date column which will be useful in maintaining history. So you can put a condition on the date column to fetch only the latest row when multiple exists for same key. Let us see how you can implement this check in Teradata: SELECT CUST_ID , CUST_NAME,CUST_CITY FROM CUSTOMERS QUALIFY ROW_NUMBER() OVER (PARTITION BY CUST_ID ORDER BY DAT_UPD DESC)=1; This query will fetch only one instance of each row. So you can add this clause at the end of any SQL query involving tables with multiple entries for same key. This way you can avoid UNIQUE PRIMARY KEY violation error.
EmailFacebookLinkedInTwitterGoogleMoreTumblrReddit Related Posted in: Teradata SQL Tutorial - Advance Tags: AVOID UNIQUE PRIMARY KEY VIOLATION ERROR,