INSERT or MULTI INSERT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: INSERT or MULTI INSERT

  1. #1
    Join Date
    Oct 2004
    Posts
    9

    INSERT or MULTI INSERT

    Hi,
    I am using Oracle 9i.
    I am trying to INSERT the records from another table and I ahve to increment the sequence_num column but it's not incrementing
    and giving me a Unique Constraint error because sequence_num is a part of the composite PK with order_id.

    I have following table structure
    =================================
    CREATE TABLE NR_ATG_ORDER (
    ATG_ORDER_ID VARCHAR2 (40) NOT NULL,
    DIVISION_ID VARCHAR2 (2) NOT NULL,
    NOPS_ORDER_ID VARCHAR2 (12),
    CUSTOMER_ID VARCHAR2 (11),
    ...
    JOB_REF_NUMBER VARCHAR2 (12),
    CONSTRAINT NR_ATG_ORDER_PK
    PRIMARY KEY ( ATG_ORDER_ID ) ) ;

    CREATE TABLE NR_ATG_ORDER_REL (
    ORDER_ID VARCHAR2 (40) NOT NULL,
    SEQUENCE_NUM NUMBER NOT NULL,
    ATG_ORDER_ID VARCHAR2 (40) NOT NULL,
    CONSTRAINT NR_ATG_ORDER_REL_PK
    PRIMARY KEY ( ORDER_ID, SEQUENCE_NUM ) ) ;

    ALTER TABLE NR_ATG_ORDER_REL ADD CONSTRAINT NR_ATG_ORDER_REL_FK1
    FOREIGN KEY (ATG_ORDER_ID)
    REFERENCES NR_ATG_ORDER (ATG_ORDER_ID) ;

    Now I have data in this table from I am loading into NR_ATG_ORDER table

    CREATE TABLE NR_ORDER_UPLOAD (
    NOPS_ORDER_ID VARCHAR2 (12) NOT NULL,
    LINE_NUMBER VARCHAR2 (4) NOT NULL,
    DIVISION_ID VARCHAR2 (2) NOT NULL,
    CUSTOMER_ID VARCHAR2 (11),
    ...
    JOB_REF_NUMBER VARCHAR2 (12),
    ORDER_FLAG VARCHAR2 (1),
    CONSTRAINT NR_ORDER_UPLOAD_PK
    PRIMARY KEY ( NOPS_ORDER_ID, LINE_NUMBER ) ) ;

    My First insert is
    ====================
    insert into nr_atg_order B
    (
    ATG_ORDER_ID,
    DIVISION_ID,
    NOPS_ORDER_ID,
    CUSTOMER_ID,
    STATUS_CODE,
    SHIPPING_METHOD,
    SHIPPING_DATE,
    ORDER_DATE,
    TRACKING_NUMBERS,
    PO_NUMBER,
    ORDER_TOTAL,
    SHIPPING_AMOUNT,
    TAX_AMOUNT,
    ORDER_TYPE ,
    ORDER_STATE,
    PAYMENT_STATE,
    JOB_REF_NUMBER
    )
    Select 'ATG_ORDER'||dynamo.ORDER_SEQ.nextval, -- generating ID
    DIVISION_ID,
    NOPS_ORDER_ID,
    CUSTOMER_ID,
    STATUS_CODE,
    CARRIER_NAME,
    SHIPPING_DATE,
    ORDER_DATE,
    TRACKING_NUMBERS,
    PO_NUMBER,
    ORDER_TOTAL,
    SHIPPING_AMOUNT,
    TAX_AMOUNT,
    null,
    'inStock',
    'processed',
    job_ref_number
    FROM NR_ORDER_UPLOAD A
    where A.NOPS_ORDER_ID not in
    (select B.nops_order_id from nr_atg_order B
    where b.nops_order_id = a.nops_order_id)
    AND job_ref_number is not null
    and job_ref_number in (select B.nops_order_id from nr_atg_order B
    where b.nops_order_id = a.job_ref_number)
    and order_flag = 1
    /

    My 2nd insert has problem
    ==========================
    insert into nr_atg_order_rel z
    (ORDER_ID,SEQUENCE_NUM,ATG_ORDER_ID)
    select a.order_id,
    (a.sequence_num)+1 ,
    c.atg_order_id
    from DYNAMO.NR_ATG_ORDER_REL a, nr_atg_order b, nr_atg_order c
    where a.atg_order_id = b.atg_order_id
    and b.nops_order_id = c.job_ref_number
    and c.atg_order_id not in (select a.atg_order_id from nr_atg_order_rel a
    where c.atg_order_id = a.atg_order_id)
    /
    Now I need to load into NR_ATG_ORDER_REL table but I am getting errors

    I have ORDER_ID, SEQUENCE_NUM and ATG_ORDER_ID columns in the table like
    ORDER_ID SEQUENCE_NUM ATG_ORDER_ID
    -------- ----------- ------------
    o27290008 0 order1001023
    o27400001 0 order1001071
    o27400002 0 order1001073

    When I Insert, for the same ORDER_ID it will increment by 1 means if the SEQUENCE_NUM is 0 then 1, if 1 then 2 and
    grab the ATG_ORDER_ID from the NR_ATG_ORDER which one I am generating during my 1st insert so records will now

    ORDER_ID SEQUENCE_NUM ATG_ORDER_ID
    -------- ----------- ------------
    o27290008 0 order1001023
    o27290008 1 ATG_ORDER52 -- new reocrds - sequence incrementing

    o27400001 0 order1001071
    o27400001 1 ATG_ORDER53 -- new reocrds - sequence incrementing

    o27400002 0 order1001073
    o27400002 1 ATG_ORDER54 -- new reocrds - sequence incrementing

    Assume that I have records inserted into ATG_ORDER table through 1st insert like

    ATG_ORDER_ID NOPS_ORDER_ID CUSTOMER_ID JOB_REF_NUMBER
    =========== ============= ============ ==============
    ATG_ORDER52 JA0072860000 RU332000000 RB0072760000
    ATG_ORDER53 CE0072850000 RU332000000 RB0072750000
    ATG_ORDER54 CE0072870000 RU332000000 RB0072760000
    ATG_ORDER55 JA0072830000 RU332000000 RB0072740000
    ATG_ORDER56 CE0072590000 RU332000000 RB0072530000
    ATG_ORDER57 HS0072840000 RU332000000 RB0072750000

    Thanks,
    Poratips

  2. #2
    Join Date
    Feb 2005
    Posts
    49
    My Friend, the problem seems to be simple hopefully but wouldn't be better if you have made easier for us by simplifiying the names and values.

    Thanks
    Aiman Al-Jumoay

Posting Permissions

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



Click Here to Expand Forum to Full Width