Create table without drop if its exist

Question

I need sone help in PL/SQl.

So my problem is, the following problem:

There is a table called: temp_table and I need to create a temp_table without drop/truncate option. It needs because all the time a table's data changing. So I know its weird, but this is necessary for my daily job.

The script work like this: The script does a text import to table, and the table is given. It use a dblink to connect the database. It works, but all the time I have to use DROP. But I need ( if its possible) to create an existing table without drop/truncate.

Can someone help me?

Thanks a lot.

Sorry for no sql code, but i think it doesn't necessary.

Answer 1

I think the concept you want is the external table. With external tables the data resides in OS files, such as CSVs. This allows us to swap data sets without dropping the table.

Find out more.

Answer 2

I take it you want to drop the table because you want to reload it, but you also want there to be as close to constant up-time as possible?

I would create two temp tables. You already have one called:

temp_table

Create another called:

temp_table_new

Load your new data into temp_table_new, then run a rename on it like so:

RENAME TABLE temp_table TO temp_table_old
 temp_table_new TO temp_table

Then

drop table temp_table_old

This will be super fast, have very little downtime, and allow you to have the functionality you've described.