Loading Data into multiple tables [message #662781] |
Fri, 12 May 2017 02:58 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
CREATE TABLE "ORDER_LINE"
( "ORDER_ID" NUMBER,
"PRODUCT_ID" NUMBER,
"QUANTITY" NUMBER,
"PRICE" NUMBER,
"PROD_NAME" VARCHAR2(50 BYTE),
"PROD_ALIAS" VARCHAR2(10 BYTE),
CONSTRAINT "COP_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID")
CONSTRAINT "PROD_ORDER_FK" FOREIGN KEY ("PRODUCT_ID")
REFERENCES "PRODUCT" ("PROD_CODE") ENABLE
)
CREATE TABLE "OL_TEMP"
( "ORDER_ID" NUMBER,
"QTY" NUMBER,
"PALIAS" VARCHAR2(10 BYTE)
)
CREATE TABLE "PRODUCT"
( "PROD_CODE" NUMBER(5,0) NOT NULL ENABLE,
"PROD_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
"PROD_DESC" VARCHAR2(50 BYTE),
"DEP_ID" NUMBER(4,0),
"CAT_ID" NUMBER(3,0),
"PROD_COST" NUMBER(5,0) NOT NULL ENABLE,
"PROD_ALIAS" VARCHAR2(26 BYTE) UNIQUE NOT NULL ENABLE,
CONSTRAINT "PROD_PK" PRIMARY KEY ("PROD_CODE")
CONSTRAINT "CAT_FK" FOREIGN KEY ("CAT_ID")
REFERENCES "CATEGORY" ("CAT_ID") ENABLE,
CONSTRAINT "DEP_FK" FOREIGN KEY ("DEP_ID")
REFERENCES "DEPARTMENT" ("DEP_ID") ENABLE
)
Example Data for OL_TEMP
order_id=1, qty=2, alias='MW'
After insert on OL_TEMP for every row inserted, when I do 'Select * from ORDER_LINE', the required output:
order_id=1, product_id=37, qty=2, price=35, prod_name= Mineral Water, prod_alias='MW'.
The problem is to add completed data in the ORDER_LINE table automatically when the data comes in OL_TEMP. If there is a way to directly insert into ORDER_LINE then even better. The main thing is to add data in ORDER_LINE.
[Updated on: Fri, 12 May 2017 02:58] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Loading Data into multiple tables [message #662808 is a reply to message #662806] |
Fri, 12 May 2017 07:19 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
Example:
File A comes to me today with 5 records. I call the procedure and add the 5 records into the ORDER_LINE.
File B comes to me Tomorrow with another 3 records. I add those 3 records in File A and only those 3 records get added into the ORDER_LINE when I call the procedure.
If I delete the 5 records which came to me earlier and then add the 3 new records ORDER_LINE table gets updated. If I don't delete the records then the constraint of the ORDER_LINE table gets violated since the procedure tries to insert all the 8 records.
[Updated on: Fri, 12 May 2017 07:22] Report message to a moderator
|
|
|
Re: Loading Data into multiple tables [message #662809 is a reply to message #662806] |
Fri, 12 May 2017 07:20 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote: Is it too complicated a way to do that? Yes, it is.
If you do as you are told, it will work. Only just, because the design is so bad.
You really need to do your data analysis and are data flow analysis. Third normal form, and all that. You need to to do that before writing any code. Then you will realize that you should always remove that file the moment you have processed it.
|
|
|
|
|
|
|
|
Re: Loading Data into multiple tables [message #662816 is a reply to message #662806] |
Fri, 12 May 2017 07:56 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
kaos.tissue wrote on Fri, 12 May 2017 08:16It's the same thing. I don't want to replace it. I just want to keep adding records and execute the procedure. The procedure inserts only the new records into the ORDER_LINE. Is it too complicated a way to do that?
If you insist on not replacing the file (the correct thing to do), so keep adding to it and get your constraint violations. Who cares? It's not hurting anything. The row doesn't get inserted.
|
|
|
|
Re: Loading Data into multiple tables [message #662818 is a reply to message #662816] |
Fri, 12 May 2017 08:18 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Or you can change the insert select to ignore rows that would violate the constraints using not exists.
But really - you need top replace the file. Opening the new file, taking the data from it and then opening the old file and adding the data is more work, and more prone to mistakes, than simply overwriting the file.
And if you want to automate that, not overwriting becomes an ever bigger pain.
|
|
|
|
Re: Loading Data into multiple tables [message #662899 is a reply to message #662841] |
Mon, 15 May 2017 06:56 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
kaos.tissue wrote on Sat, 13 May 2017 01:15Ok I got it. Its better to just replace the file. How do I close the threads? I got all the answers I needed. i couldn't find any option here to close the topic.
Unlike the OTN forums, there is no way to "mark correct" or close a thread. Just say 'thanks', perhaps summarize what you learned, and let it go.
|
|
|