Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: loading data into tables from tables..

    Can anyone please tell me about the different methods we can load data from tables into tables in Oracle...
    Thank you in advance.

    Regards,
    Asmitha

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    INSERT INTO table_one (col1, col2, col3) SELECT c1,c2,c3 FROM table_two

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

    Cool


    COPY FROM ... TO ... {APPEND|CREATE|INSERT|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
    Furthermore, CTAS could be another option:

    CREATE TABLE second_table AS SELECT * FROM first_table;

    Export and Import utilities could do the work, but you can't specify different table name in that case (however, those utilities can be used to transfer data between two tables with the same name but different users).

    Moreover, there's a possibility to create a CSV file by spooling the result of the SELECT statement and import such a file into another Oracle table using either SQL*Loader or External table feature.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    Thank you so much for the replies. But what if I'm loading data from table A to table B, and Table B has more columns when compared to table A and both are in two different databases?

    I actually created a dblink to get the columns from tabA to tabB and then added the additional columns in tab B. Now I have to populate the table B with data in table A.....How do I accomplish this?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    insert into table1 (col1,col2,col3,col4)
    select col3,col1,col24,col7
    from table2@mylink
    where .....;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jul 2005
    Posts
    276
    The tables I'm loading has approximately 100 columns and 150 rows....
    Last edited by nandinir; 10-03-06 at 15:44.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by nandinir
    The tables I'm loading has approximately 100 columns and 150 rows....
    So what?

    You can still use the solution posted by beilstwh

  9. #9
    Join Date
    Jul 2005
    Posts
    276
    What I said was for LittleFoot as it was taking a lot of time for me to spool the data.....
    I got it, but you have to mention a null in the select statement.
    insert into table1 (col1,col2,col3,col4)
    select col1,col2,col3,' 'col4
    from table2@mylink
    where .....;

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You don't have to name the columns in the select. Simply use null for a column that will always be null.


    insert into table1 (col1,col2,col3,col4,col5)
    select col1,col2,col3,null,'XXX'
    from table2@mylink
    where .....;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Mar 2004
    Posts
    370

    Exclamation

    beilstwh and littleFoot's solutions are all OK and 100% valid, imho.
    do not use COPY statement b/c Oralce has announced for years that it will be depricated as soon
    The tables I'm loading has approximately 100 columns and 150 rows....
    Really smells bad for me . Consider normalization of your relations at first step. I can't imagine any simple fact in real world that needs 100 property. It is the sign of information mix-up.
    -Good luck

  12. #12
    Join Date
    Jul 2005
    Posts
    276
    Hi All,

    I have a simple question pertaining to dataload.
    I'm trying to copy data from tab2(only one column of the 3 columns present) to tab1(15 columns). How can I do this?
    Any help is greatly appreciated.

    Thanks in advance!

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use the method people have already given you above a few times:
    Code:
    insert into tab1 (colX) select col1 from tab2;
    or similar. What is holding you back?

    If there are other NOT NULL columns in tab1 without defaults then you'll need to default them yourself:
    Code:
    insert into tab1 (colX, colY) select col1, 'ABC' from tab2;

Posting Permissions

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