INSERT or MULTI INSERT

From: poratips shah via DBMonster.com <forum_at_nospam.DBMonster.com>
Date: Fri, 27 May 2005 05:40:26 GMT
Message-ID: <cc73a95d2b314a31ae66566e7df2ee34@DBMonster.com>

Hi,
I am using oracle 9 r2.
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

-- 
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/oracle/200505/1

Received on Fri May 27 2005 - 00:40:26 CDT