DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: move data from one table to another

  1. #1
    Join Date
    Aug 2002
    Posts
    115

    move data from one table to another

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2002
    Posts
    115
    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.

  4. #4
    Join Date
    Jan 2006
    Posts
    39
    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.

  5. #5
    Join Date
    Sep 2005
    Location
    Philippines
    Posts
    21
    16Million records
    i would suggest export - import.... using table export and
    table import option only.

    hope this helps...

    Quote Originally Posted by 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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by topgun
    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 ...

  7. #7
    Join Date
    Sep 2005
    Location
    Philippines
    Posts
    21
    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...


    Quote Originally Posted by davey23uk
    why do it the slow way?

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

  8. #8
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    exchange partition

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width