Insert in append mode
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Insert in append mode

Hybrid View

  1. #1
    Join Date
    Apr 2009
    Posts
    51

    Insert in append mode

    Hi folks,

    i have 2 tables named F571226, F751226_10march in two different schema testdta, backupdta respectively
    Note: F571226 doesnt have any data in testdta schema, both have same columns names
    Now my requirement is to insert data into F571226 from F571226_10march

    but when i try to insert by below command it taking too time and failed

    insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14

    shall i specify each field (columns name) instead of specifying source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows


    please correct my syntax if it is wrong

    insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.


    thanks & regards

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    Unless the table is partitioned, I don't see how the parallel hint will help. Since the destination table is empty,
    you might want to get the DDL for the indexes and constraints, drop them then do the insert without parallel.
    You can then create the indexes and constraints.

    If the indexes and constraints are in place during the insert ever row needs to be checked to see if it is violating
    a constraint and each index needs to be updated for each row.
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by younusdba View Post
    ... and failed...
    Could you please share the returned error stack, please?

    Quote Originally Posted by younusdba View Post
    shall i specify each field ...
    No, both tables have the same structure therefore no need to specify column by column.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2014
    Posts
    3
    You can try using Datapump (impdp) with TABLE_EXISTS_ACTION and REMAP_SCHEMA import options -
    http://www.oraclesql-plsql.com/Datapump.html

    Hope this solves your issu

  5. #5
    Join Date
    Mar 2014
    Posts
    3
    Quote Originally Posted by younusdba View Post
    Hi folks,

    i have 2 tables named F571226, F751226_10march in two different schema testdta, backupdta respectively
    Note: F571226 doesnt have any data in testdta schema, both have same columns names
    Now my requirement is to insert data into F571226 from F571226_10march

    but when i try to insert by below command it taking too time and failed

    insert /*+ APPEND PARALLEL*/ into testdta.F571226 value select * from backupdta.F571226_10march14

    shall i specify each field (columns name) instead of specifying source table (backupdta.F571226_10march) in the above syntax?. source table has got around 25000000 rows


    please correct my syntax if it is wrong

    insert /*+ APPEND PARALLEL */ nologging into testdta.F571226 value select col1, col2,col3,col4,col5 ................... from backupdta.F571226_10march.


    thanks & regards

    You can try using Datapump (impd) with TABLE_EXISTS_ACTION and REMAP_SCHEMA import parameters -
    http://www.oraclesql-plsql.com/Datapump.html

    Hope this would help

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool CTAS is the way.

    Quote Originally Posted by younusdba View Post
    Hi folks,
    i have 2 tables . . . F571226 doesnt have any data
    . . . E t c . . .
    Try CTAS:
    Code:
    DROP TABLE Testdta.F571226 CASCADE CONSTRAINTS;
    
    CREATE TABLE Testdta.F571226
    NOLOGGING
    AS
       SELECT *
         FROM Backupdta.F571226_10march;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Tags for this Thread

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