Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    29

    Unanswered: Create new table from other tables in oracle

    Hi
    Pls can someone assist on how to create a new table existing multiple oracle tables. The new table and the existing ones all have same no of column and column names. Also if it is otherwise, how can one create new table from existing ones with disimilar no of column and column names.
    Regards ,
    'Soga

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    CREATE TABLE new_table
    AS
    SELECT column_1, column_2, column_5
    FROM existing_table;
    or if you want to rename columns
    Code:
    CREATE TABLE new_table
    AS
    SELECT column_1 as first_column, 
           column_2 as second_column, 
           column_5 as third_column
    FROM existing_table;
    More details in the manual

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    and if you only want the structure, but not the data.

    CREATE TABLE new_table
    AS
    SELECT column_1, column_2, column_5
    FROM existing_table
    where 1=2;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Nov 2007
    Posts
    29

    Red face create table from multiple tables

    Is there no simpler way?
    I have about 9 tables each with 112 columns. the columns are all same name, same datatype and i want to merge all the tables together into one big table since they have equal no of columns and column name. So i need to create a new table from those multiple tables not just s single table with few columns.
    I want to popluate at thesame time the new table with all the data in the multiple tables.
    thank you
    regards
    'soga

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    That is NOT what you asked, however

    Code:
    create table x as
    select * from table_a
    union all
    select * from table_b
    union all
    select * from table_c;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Nov 2007
    Posts
    29

    Thumbs down Thanks

    Thanks a lot for the sample query, it worked well
    regards
    'soga

Posting Permissions

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