INSERT or MULTI INSERT

[poratips]

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

[jomoueea]

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