Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    London
    Posts
    76

    Unanswered: inserting tables from one schema to another

    Hi there, I need to export tables from one schema and import them into another test schema for testing some procedures

    How can I do this? I have Toad, I assume I can use the exp funtion but what is the syntax to just export and import tables.

    Do I have to truncate all tables in the schema the tables will be imported to first?

  2. #2
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    if you have toad, you can generate the script for the table and then execute the same in the target schema.....

  3. #3
    Join Date
    Feb 2004
    Location
    Bucharest, Romania
    Posts
    14
    Hi,

    Let's suppose we have the A and B schemas and you want to copy the tables from A to B.
    For this reason you just to ensure you have the correct "grant" permission for the B schema for all tables from A schema that you want to action. For example:
    grant insert on X to A;
    where X is the name of the table. This you should run from the B schema.

    Afterwards, you just run (in the B schema) the creation script for the table (you can get from TOAD) and then:
    (A schema): grant select on X to B;
    Then:
    (B schema): insert into B.X select * from A.x; (from A schema)

    Hope this helps.

    Adrian

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    In test schema, you could 1. drop tables

    2. Export Schema from good database

    3. imp fromuser=productionschema touser=testschema

    HTH
    Gregg

  5. #5
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    is it a schema or instance that you need to insert table into....

    bagchi

Posting Permissions

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