Skip to Main Content
  • Questions
  • how can we load the data in to that table without performing truncate operation

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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

Comments

External Table? Sure. Merge?... maybe...

Duke Ganote, August 09, 2016 - 4:02 pm UTC

If it's a fairly constant 70k records, most of the data is either 'updated' or unchanged; probably faster to just:
1. insert into a 'work' table
2. 'backfill' any missing records (if desired)
3. partition swap with the 'real' table.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9528335800346784022

Chris Saxon
August 10, 2016 - 9:50 am UTC

Yep, that could work too. 70k rows is pretty small these days though. I would expect most approaches to be "fast enough".