Duplicate Unique Prime Key Error In Table
Contents |
baseUsers turn on suggestions Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results duplicate unique prime key error in target table for Search instead for Did you mean: Teradata : Product
Duplicate Unique Prime Key Error In Teradata
Forums : Database : Error from Insert Select statement Options Subscribe to RSS Feed Mark
Failure 2801 Duplicate Unique Prime Key Error
Topic as New Mark Topic as Read Float this Topic to the Top Bookmark Subscribe Printer Friendly Page donal28 Enthusiast Options Mark as New Bookmark
Duplicate Unique Key Error Wow
Subscribe Subscribe to RSS Feed Get Direct Link 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 DDEWP42P.FMP_STRATEGIC_CM_BORROWER_AUTO (BORROWER_NAME) select DISTINCT TRIM duplicate unique prime key error informatica (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 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 duplicate unique prime key error world of warcraft Meta Discuss the workings and policies of this site About Us duplicate unique primary key error in informatica Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with teradata odbc teradata driver teradata database duplicate unique prime key error us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just http://community.teradata.com/t5/Database/Error-from-Insert-Select-statement/td-p/57936 like you, helping each other. Join 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 INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY http://stackoverflow.com/questions/11727972/teradata-identity-column-and-duplicate-unique-prime-key-error-in-dbname-tablena 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 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:0
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 using SQL Joins, we must take care http://usefulfreetips.com/Teradata-SQL-Tutorial/remove-duplicate-unique-primary-key-error/ 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 the table is maintaining history of CUSTOMERS, so it will be having multiple entries for duplicate unique 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 how to avoid such situation ? The easiest way of overcoming this issue is to fetch the latest entry of CUST_ID duplicate unique prime 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, DUPLICATE UNIQUE PRIMARY KEY ERROR after SQL JOINS, TERADATA SQL TUTORIAL Page with Comments Pingback: Teradata interview question | Teradata Leave a Reply Cancel reply Post navigation Previous post: « Previous Next post: Next » Subscribe to Blog via Email Enter your emai