Thanks for the question, sona.
Asked: August 08, 2016 - 8:43 am UTC
Last updated: August 10, 2016 - 9:50 am UTC
Version: oracle 10.1.2
Viewed 1000+ times
You Asked
Hi ,
I have a table which contains 70000 records ,how can we load the data in to that table without performing truncate operation.
Can we do using merge command to do the update and insert,we need to update,insert and delete .The table should not be empty at any point of time.
so truncate i am not preferring.
I am importing the data from a excel file using toad,the excel file has around 70000 records,it is taking lot of time to load in the database.
I want this process to happen without execution of truncate command,Can i use the concept of update,insert and delete the old data which is already loaded.
for eg: if we have one table :
say tbl_sample
i have loaded the table which contains data like
tbl_sample
1 2
2 3
6 8
now next week i want to load the other set of data without truncating the table.
so that
tbl_sample
1 2
2 3
6 8
gets deletd and loaded with new set of data.
and one more thing i wanted to add on the top ,if i import the excel file to load in to database using toad ,it is taking 5-6 hours to load ,can you please provide some solution to optimize the time load.
and Chris said...
There's nothing requiring you to use truncate. Indeed, if you need the data to appear populated at all times, then delete+insert or merge is the way to go!
Instead of using TOAD, I'd recommend the following method:
- Convert the Excel file to CSV
- Transfer it to the database server
- Create an external table over the top of it
- Load the data into the real with a process like:
delete from tab;
insert into tab
select * from ext_tab;
Or you could make it a merge instead. If only a small amount of data changes, this will probably be faster.
5-6 hours to load 70,000 is incredibly slow. Try the external table process above. If its still taking a long time then trace your session, run tkprof the trace file and post the results here.
https://blogs.oracle.com/sql/entry/how_to_create_an_execution#tkprof
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment