Thread: table import

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    20
    Rep Power
    0

    table import


    am trying to migrate table A to table B from same instance but different schema. if the table have different structure and some different column is it possible to do the migration of data? for example coulmn 1 in table A has different fields then coulmn 1 in table B.
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    You mean by import utility of oracle? If yes, then you cannot do so. But if the datatype is different and compatible then you can import it using IGNORE=Y option with your import command. Remember column name should be identical.

    How many tables need to be imported? If only 5-10, then you can load the data between schemas by using INSERT command, no matter your columns names are different but dtattype should be identical or compatible. For example you have one table 'Employees' that has different structure then most popular table 'EMP', then your query would be look like:

    Employees:
    Employee# varchar2(3)
    Ename varchar2(25)
    salary number(5)
    Department number(2)

    INSERT INTO Employees (SELECT empno,ename,sal,dept FROM emp);

    Regards
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    As mehak said, the tables are in different schemas.
    So, one way to take care of it is by specifying the schema name as in:

    INSERT INTO Employees (SELECT schema_name.empno,ename,sal,dept FROM emp);

    Another way to do it is via synonyms.

    The schema from which you run your INSERT statement needs a select privilege on the other schema.

    Cheers,
    Dan
  6. #4
  7. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    Thanks dan to indicate my mistake.

    Sorry mehak, my query would be look like that:

    INSERT INTO Employees (SELECT empno,ename,sal,dept FROM schema_name.emp);

IMN logo majestic logo threadwatch logo seochat tools logo