Home » SQL & PL/SQL » SQL & PL/SQL » Loading Data into multiple tables (Oracle 11g R2, pl/sql)
icon5.gif  Loading Data into multiple tables [message #662781] Fri, 12 May 2017 02:58 Go to next message
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 #662792 is a reply to message #662781] Fri, 12 May 2017 04:03 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
I've suggested in previous topics that you need to correct your data analysis before writing any code. First question: why are you storing the PRODUCT_NAME in both ORDER_LINE and PRODUCT?
Re: Loading Data into multiple tables [message #662798 is a reply to message #662792] Fri, 12 May 2017 06:44 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yes I got it. I had added 'prod_name' int he order_line just for the time being to make things more understandable. I removed it now and I finally am able to do it.

I created an external table and wrote a procedure to join and insert into order_line. The only thing I need to do is to delete data from the external file before inserting new data so that no constraint is violated while joining and adding data into order_line.

Is there a way by which I don't have to delete the data from the external file and insert only the new data that comes in everyday into the order_line table?
Re: Loading Data into multiple tables [message #662801 is a reply to message #662798] Fri, 12 May 2017 07:04 Go to previous messageGo to next message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
How is the file getting populated?
Re: Loading Data into multiple tables [message #662804 is a reply to message #662801] Fri, 12 May 2017 07:11 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
It comes to me via email and then I add the data into the external file.

The file name is 'abc.txt'. It contains partial data as we have discussed in earlier threads. The data from this is joined with PRODUCT table and inserted into the ORDER_LINE through a procedure.

If I don't delete the data before executing the procedure the second time, the constraints of the ORDER_LINE table are violated as it tries to add the same PK again. In order to add the data cleanly I need to delete the data in the abc.txt file. Is there a way by which I can only insert the new records in the external table into the ORDER_LINE.
Re: Loading Data into multiple tables [message #662805 is a reply to message #662804] Fri, 12 May 2017 07:13 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
Quote:
If I don't delete the data before executing the procedure the second time,
This is painful.
Don't delete anything. Just replace the damn file with the new one.
Re: Loading Data into multiple tables [message #662806 is a reply to message #662805] Fri, 12 May 2017 07:16 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
It'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?
Re: Loading Data into multiple tables [message #662808 is a reply to message #662806] Fri, 12 May 2017 07:19 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7262
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 #662810 is a reply to message #662806] Fri, 12 May 2017 07:20 Go to previous messageGo to next message
EdStevens
Messages: 902
Registered: September 2013
Senior Member
kaos.tissue wrote on Fri, 12 May 2017 07:16
It's the same thing. I don't want to replace it.
Why not? You have already added that data to your database. The external file you got yesterday has already served its purpose. Unless there is still more that you haven't told us.
Re: Loading Data into multiple tables [message #662811 is a reply to message #662808] Fri, 12 May 2017 07:22 Go to previous messageGo to next message
John Watson
Messages: 7262
Registered: January 2010
Location: Global Village
Senior Member
And if you persist in saying "record" when you mean "row" you will (a) never become a relational engineer and (b) irritate the heck out of real relational engineers.
Re: Loading Data into multiple tables [message #662812 is a reply to message #662810] Fri, 12 May 2017 07:23 Go to previous messageGo to next message
EdStevens
Messages: 902
Registered: September 2013
Senior Member
For those that may not have picked up on it, the OP is running no less than three threads on this problem.

'Trigger to join and insert data into table'
'Instead of Trigger'
'Loading Data into multiple tables'
Re: Loading Data into multiple tables [message #662813 is a reply to message #662810] Fri, 12 May 2017 07:24 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I can do that. I can replace the file. But I just want to know how can it be done without replacing it. I'm just learning so I want to know all the options.
Re: Loading Data into multiple tables [message #662814 is a reply to message #662813] Fri, 12 May 2017 07:26 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@John
Ok I got it. Its "row" from now onward till the time I die. and thanks! to all of you guys. Really got me through it.
Re: Loading Data into multiple tables [message #662816 is a reply to message #662806] Fri, 12 May 2017 07:56 Go to previous messageGo to next message
joy_division
Messages: 4822
Registered: February 2005
Location: East Coast USA
Senior Member
kaos.tissue wrote on Fri, 12 May 2017 08:16
It'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 #662817 is a reply to message #662813] Fri, 12 May 2017 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
kaos.tissue wrote on Fri, 12 May 2017 05:24
I can do that. I can replace the file. But I just want to know how can it be done without replacing it. I'm just learning so I want to know all the options.
so do not replace the file & see what happens
Re: Loading Data into multiple tables [message #662818 is a reply to message #662816] Fri, 12 May 2017 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13014
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 #662841 is a reply to message #662818] Sat, 13 May 2017 01:15 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Ok 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.
Re: Loading Data into multiple tables [message #662899 is a reply to message #662841] Mon, 15 May 2017 06:56 Go to previous message
EdStevens
Messages: 902
Registered: September 2013
Senior Member
kaos.tissue wrote on Sat, 13 May 2017 01:15
Ok 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.
Previous Topic: filesystem_like_logging is not in ddl of table
Next Topic: ListAgg results
Goto Forum:
  


Current Time: Thu Feb 22 00:00:55 CST 2018

Total time taken to generate the page: 0.03799 seconds