Results 1 to 4 of 4

Thread: insert script

  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: insert script

    i am trying to insert new field values into Table_y_name for all the T_NUM's in table Table_x_name and i am getting a unique constraint error

    The V_ID,C_ID,T_NUM and field_name is the primary key for Table_x_name.

    can u please suggest y ?

    the script i am using is ....




    Declare
    begin
    FOR hint IN (select V_ID,C_ID,T_NUM from Table_x_name)
    LOOP


    INSERT INTO Table_y_name ( V_ID,
    C_ID,
    T_NUM,
    SECTION_NAME,
    DISPLAY_ORDER,
    FIELD_NAME,
    SYSTEM_FIELD_FLAG,
    REQUIRED_FLAG,
    EDITABLE_FLAG,
    SHOW_IN_PAYMENT_FORM_FLAG,
    SHOW_IN_RECEIPT_FLAG,
    SHOW_IN_CUSTOMER_EMAIL_FLAG,
    SHOW_IN_MERCHANT_EMAIL_FLAG,
    SHOW_IN_VT_FLAG,
    FIELD_SIZE,
    DISPLAY_NAME,
    FORWARD_ON_APPRVL_FLAG,
    FORWARD_ON_DECL_FLAG,
    FIELD_TYPE,
    DD_RADIO_VALUES,
    FIELD_SIZE_MIN)

    SELECT hint.V_ID,
    hint.C_ID,
    hint.T_NUMBER,
    'xyzz' SECTION_NAME,
    (SELECT MAX(DISPLAY_ORDER)+1 FROM Table_y_name_FIELD WHERE V_ID=Table_y_name.V_ID AND COMPANY_ID=Table_y_name.C_ID AND TERMINAL_NUMBER=Table_y_name.T_NUM AND section_name='xyzz') DISPLAY_ORDER,
    'new field' AS FIELD_NAME,
    'Y' AS SYSTEM_FIELD_FLAG,
    'N' AS REQUIRED_FLAG,
    'Y' AS EDITABLE_FLAG,
    'Y' AS SHOW_IN_PAYMENT_FORM_FLAG,
    'N' AS SHOW_IN_RECEIPT_FLAG,
    'N' AS SHOW_IN_CUSTOMER_EMAIL_FLAG,
    'N' AS SHOW_IN_MERCHANT_EMAIL_FLAG,
    'Y' AS SHOW_IN_VT_FLAG,
    1 AS FIELD_SIZE,
    'Field' AS DISPLAY_NAME,
    'N' AS FORWARD_ON_APPRVL_FLAG,
    'N' AS FORWARD_ON_DECL_FLAG,
    'DDLIST' AS FIELD_TYPE,
    'Y=Yes:N=No' AS DD_RADIO_VALUES,
    1 AS FIELD_SIZE_MIN
    from
    (select 1 region_id from dual union select 2 from dual) region;
    end loop;
    end;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data, we can't run, test or improve posted code.

    >i am getting a unique constraint error
    DUPLICATE values result in error above.
    SELECT needs to ensure that DUPLICATE does not exist in result set.
    This is a data dependent error.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2012
    Posts
    4
    I first ran a delete and then ran the insert script

    My question is when I am selecting the V_ID, C_ID and T_NUMB from Table_x_name ,May be those are already existing in the Table_y_name
    . But primary key is a combination in V_ID, C_ID and T_NUMB and Field_name.

    So Is there a different way i can insert the new field to the table_y_name for all the V_ID, C_ID in table_y_name without duplicating them.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It might be easier if you used MERGE instead

    MERGE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •