DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: insert from multiple tables

  1. #1
    Join Date
    Jan 2002
    Posts
    83

    insert from multiple tables

    hi all


    I need to insert data from two tables into one table, 15 colums from each table. In all 30 columns from both the tables.

    Can anyone help me with the syntax ????



    thankx
    ICEMAN

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865
    stab1, stab2 >> into target

    insert /* +append */
    into target(15 columns separated by commas)
    select col1, col2... from stab1;

    insert /* +append */
    into target(15 columns separated by commas)
    select col1, col2... from stab2;

    HTH.

  3. #3
    Join Date
    Jan 2002
    Posts
    78
    If you can able to join these two tables basing on some columns then it is easy ; otherwise one way I know is :

    SQL> desc table1;
    Name Type
    ----------------------------------------- ----------------------
    A VARCHAR2(10)
    B VARCHAR2(10)

    SQL> select * from table1;

    A B
    ---------- ----------
    A1 B1
    A2 B2

    SQL> desc table2;
    Name Type
    ----------------------------------------- ----------------------
    C VARCHAR2(10)
    D VARCHAR2(10)

    SQL> select * from table2;

    C D
    ---------- ----------
    C1 D1
    C2 D2


    SQL> create table table3 as select a,b,Null C,NULL D from table1 UNION select NULL a, NULL b, C,D from table2;

    Table created.

    SQL> desc table3;
    Name Type
    ----------------------------------------- ----------------------------
    A VARCHAR2(10)
    B VARCHAR2(10)
    C VARCHAR2(10)
    D VARCHAR2(10)

    SQL> select * from table3;

    A B C D
    ---------- ---------- ---------- ----------
    A1 B1
    A2 B2
    C1 D1
    C2 D2

  4. #4
    Join Date
    Feb 2002
    Posts
    70
    Hello iceman,
    Does both the tables have any common column on which you can join.
    If you have then its easy to insert into another table.

    Example:
    Table: XYZ
    Columns: a number,
    b number

    Table: ABC
    Columns: x number,
    y number

    and columns 'a' and 'x' are common then

    Table: TEMP
    columns: a number,
    b number,
    x number,
    y number

    insert into temp
    select a.a, a.b, b.x, b.y
    from xyz a, abc b
    where a.a = b.x;

    I hope this help you.

    Thanks,
    ________________
    ShanDJ

  5. #5
    Join Date
    Jan 2002
    Posts
    83
    hi there

    yes there is a common column in the two tables.


    regards
    ICEMAN

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