Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2015
    Posts
    1

    Unanswered: copy one table data to another table

    hi all, i am new to oracle. By using shell script. i would need to copy one table data to the another table with the same data by overwritten.

    please let me know how to write by using sql queries.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    using just queries will cause a bit of overhead and logging, might want to look into load replacing. The manuals are a great place to start.
    Dave

  3. #3
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by arun888 View Post
    hi all, . . e t c . . . i would need to copy one table data to the another table with the same data by overwritten. . . .
    1) Copying source table to target table:

    1.1) Target and source in same database:

    1.1.1) Target and source in same schema:
    TRUNCATE TABLE Target;
    INSERT INTO Target /*+APPEND */ SELECT * FROM Source;

    1.1.2) Target and source in different schema:
    TRUNCATE TABLE TGTSchema.Target;
    INSERT INTO TGTSchema.Target /*+APPEND */
    SELECT * FROM SRVSchema.Source;

    1.2) Target and source in different database:

    1.2.1) Create db link on target for source, then execute:
    TRUNCATE TABLE TGTSchema.Target;
    INSERT INTO TGTSchema.Target /*+APPEND */
    SELECT * FROM SRVSchema.Source@SRCDBLink;

    1.2.2) Use expdp/impdp
    expdp / directory=expdp_dir tables=SRVSchema.Source;
    impdp / directory=expdp_dir full=y table_exists_action=replace;

    1.2.3) Use impdp over network link:
    impdp / directory=expdp_dir network_link=SRCDBLink tables=SRVSchema.Source \
    table_exists_action=replace;
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What should be the final result? Two tables with the same contents, or will they be different (because the target table already contains some records you want to keep)?

    If the former, CTAS is probably the simplest option:
    Code:
    create target_table as select * from source_table
    If the later, omit existing records, such as
    Code:
    insert into target_table (id, col2, col3, ...)
    select id, col2, col3, ...
    from source_table
    where id not in (select id from target_table)
    because "overwriting" (as you said) might not work, especially if the target table contains primary or unique key/index which would fail as soon as duplicate is being inserted. You could workaround it with PL/SQL and exception handling, but that would probably be slower than pure SQL.

    Yet another option is to use MERGE statement.

    Finally, saying that you use a shell script: any SQL code you write (and you're satisfied with - now you have several options to think about) should be saved into a .SQL file, and then you'd call that SQL script from your shell script.

Posting Permissions

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