Teradata Procedure throws Failed [5758 : HY000] MyProcedure
<p>I am facing an issue while executing a Terdata store-procedure that I am working on. </p>

<p>The purpose of this sproc is to move data from source table to a destination table (Staging to Actual) as part of a ETL Process</p>

<p>My source table look something like this:
    <blockquote>
    <dl>
    <dd>ACTUAL_ITEM_COMMENTS</dd>
    <dd>ITEM_NUM VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,</dd>
    <dd>DESCRIPTION VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,</dd>
    <dd>DESCRIPTION_SEQUENCE INTEGER,</dd>
    <dd>DESCRIPTION_TYPE VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC</dd>
    </dl>
    </blockquote></p>

<p>and my destination table looks like this:
    <b>[UPDATED] Section</b>
    <blockquote>
    <dl>
    <dd>ITEM_COMMENT_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY<br>
    &nbsp;&nbsp;&nbsp;&nbsp;(START WITH 1<br> 
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;INCREMENT BY 1 <br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MINVALUE -2147483647<br> 
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAXVALUE 2147483647<br> 
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NO CYCLE),</dd>
    <dd>STAGING_ITEM_COMMENTS</dd>
    <dd>ITEM_CD VARCHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC,</dd>
    <dd>ITEM_COMMENT_TXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,</dd>
    <dd>ITEM_COMMENT_TYPE_CD CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC</dd>
    </dl>
    </blockquote></p>

<p><b>[UPDATED] Section</b></p>

<p>It seems that MERGE statement in Teradata do work fine when not mapping all fields while inserting (NOT MATCHING) data to the destination table</p>

<p>As I describe my destination table it includes an IDENTITY columns which I am not mapping in my <b>MERGE statement</b> which makes the procedure to throw an error like:</p>

<blockquote>
  <p><b>(Executed as Single statement.  Failed [5758 : HY000] MyProcedure:The search >condition must fully specify the Target table primary index and partition >column(s) and expression must match INSERT specification primary index and >partition column(s). 
  Elapsed time = 00:01:33.616</b></p>
</blockquote>

<p>So I decided to try with a similar table as my destination by removing the IDENTITY column. </p>

<pre><code>MERGE INTO "ODS_TABLES_TEST"."ITEM_COMMENT_TEST" ic   -- ITEM_COMMENT_TEST has no identity column defined on its DDL
USING 
(
    SELECT 
        "ITEM_NUM"
        ,"ITEM_COMMENT_TYPE_CD"
        ,"ITEM_COMMENT_TXT"
        ,'1048 ITEM COMMENT' AS "UPDATE_INTERFACE_NM"
        ,CURRENT_TIMESTAMP AS "CREATE_DTTM"
        ,CURRENT_TIMESTAMP AS "UPDATE_DTTM"
    FROM
        "ZZ_ITEM_COMMENT_FINAL"
) icb 
ON (icb."ITEM_NUM" = ic."ITEM_NUM" AND icb."ITEM_COMMENT_TYPE_CD" = ic."ITEM_COMMENT_TYPE_CD")
WHEN MATCHED THEN UPDATE SET "ITEM_COMMENT_TXT" = icb."ITEM_COMMENT_TXT"
WHEN NOT MATCHED THEN INSERT ("ITEM_NUM", "ITEM_COMMENT_TYPE_CD", "ITEM_COMMENT_TXT", "UPDATE_INTERFACE_NM", "CREATE_DTTM", "UPDATE_DTTM" ) 
                       values(icb."ITEM_NUM", icb."ITEM_COMMENT_TYPE_CD", icb."ITEM_COMMENT_TXT", icb."UPDATE_INTERFACE_NM", icb."CREATE_DTTM", icb."UPDATE_DTTM") ;
</code></pre>

<p>Do I need always to map all columns while inserting? </p>