Import data from Table A into Table B
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Import data from Table A into Table B

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    Import data from Table A into Table B

    Hi

    I am trying to import Data from table A to Table B in the same schema. but so far I see no docs about the related task?
    Any suggestion?
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Hmm why you need an import for that?!?!

    Isnt insert select very easy???

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Insert into tableB
    select whatever from tableA
    where . . . . . .

    . . . but I've a sneeking feeling that's not what you want.

  4. #4
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Thanks for the prompt response.
    Here is the problem.

    We are trying to do some reorg.

    I moved most the tables into a new created LMT.

    I have attempted to move a couple of tables and was getting ora- 00997
    illigal use of LONG datatype.

    So I created a new table with varchar2 (2000)in the new LMT and trying to get the data into it.


    SQL> insert into Table B SELECT * FROM A;
    Failed with *
    ERROR at line 1:
    ORA-00997: illegal use of LONG datatype

    any other easy way and advise will be highly appreciated.
    Thanks
    Last edited by Ablakios; 12-30-2004 at 12:12 PM.
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    use copy but be careful with decimals

  6. #6
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Pando
    I thought sqlplus copy is from one database to another using sqlnet connection? I might be mistaken. Can you please send me an example?
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  7. #7
    Join Date
    Dec 2004
    Posts
    10
    You can use copy just reference the same tnsnames.ora entry.
    Here is the documentation location: http://download-west.oracle.com/docs...apb.htm#634246


    Here is the example in the documentation:

    COPY FROM HR/your_password@BOSTONDB -
    TO HR/your_password@BOSTONDB -
    INSERT EMPLOYEE_COPY2 -
    USING SELECT * FROM EMPLOYEE_COPY

    I have done this is the past just be careful to run initial test on a smaller set of data before going for a larger set. Also the dashes are necessary to indicate next line.

  8. #8
    Join Date
    Dec 2004
    Posts
    4

    How about create table or export import????

    About about doing a create table as select * from ???
    ---------------------------
    SQL> create table capper as select 'GGGGG' col1, 1000.11 col2 from dual;

    Table created.

    SQL> select * from capper
    2 ;

    COL1 COL2
    ----- ----------
    GGGGG 1000.11

    SQL> create capper2 as select * from capper;
    create capper2 as select * from capper
    *
    ERROR at line 1:
    ORA-00901: invalid CREATE command


    SQL> create table capper2 as select * from capper;

    Table created.

    SQL> select * from capper2;

    COL1 COL2
    ----- ----------
    GGGGG 1000.11

    SQL>
    -----------------------------


    Or you could export import the table.

    regards,

    Adam
    ______________________________
    Adam Capper
    Oracle DBA

  9. #9
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    eyen1
    Thank you very much. it was successful.
    Again Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

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