move data from one table to another

[aspdba]

Hi,

What is the fastest way to move data from one table to another in the same schema.

like move data from table A to table B.

No fo records=16 million.

Thanks
Sam

[slimdave]

Same structure of table? Into an empty table or preserving the current rows of the target table? You need to keep the data in the old table still?

[aspdba]

yes...and the target table is partitioned.

I need to insert records into 1 partition..which is empty right now..and need to preserve the data in the other partitions.
Structure is the same of both the tables.

[traveller]

Why do you just drop that empty table ( call table_b), then do

create table_b as select * from table_a. It will create a table_b with the same name you drop and the data that contains in table_a.

[topgun]

16Million records
i would suggest export - import.... using table export and
table import option only.

hope this helps...

Hi,

What is the fastest way to move data from one table to another in the same schema.

like move data from table A to table B.

No fo records=16 million.

Thanks
Sam

[davey23uk]

16Million records
i would suggest export - import.... using table export and
table import option only.

hope this helps...

why do it the slow way?

its within the same database so that will always be slower than insert /*+append*/ into ...

[topgun]

thanks for the inputs sir davey23uk.... i am still new with Oracle DBA so i usually use exp/imp for big tables... but it seems that using SQL hints is also faster...

why do it the slow way?

its within the same database so that will always be slower than insert /*+append*/ into ...

[davey23uk]

just think about it, import does normal inserts so why take the data out of the database just to put it back in.

The append hint generates no redo and goes right to the datafiles - nice and quick

[slimdave]

If the tables have the same columns and the target table is partitioned, then a partition exchange would be possible. You'd have to have the same null attributes etc., but that ought to be easy to arrange

[wdfloyd]

I think the fastest way would be the partition exchange that was suggested. Look in the documentation for the alter table exchange partition syntax. There are also some examples on asktom.