How to create a table from existing table in database

We can create duplicate of table currently present in the database with the utilization of Data Definition language and Data modification language. CREATE and SELECT statements are used, Additionally INSERT statement is used to copy table into existing table. The new table has same columns. We can choose some specific columns or all columns. At the point when the new table is made with the old table, the new table will have the values of the old table.

Syntax

create table new_table_name as select * from old_table_name;

Copying table structure only and not the table data

We need to just use a where clause that won't select any rows:

create table new_table_name as select * from old_table_name where 1=2;

Example

Let us create copy of students table structure  in students_copy

create table students_copy as select * from students where 1=2;

select * from students;

select * from students_copy;

We can see that only the structure is copied, no rows are present in students_copy table as present in students table. To avoid iterate again and again and insert nothing based on the condition where 1=2; we can execute below SQL statements:

create table students_copy as select * from xyz where rownum = -1;

create table students_copy as select * from students where rownum = 0;

create table students_copy as select * from students where rownum <1;

Inserting rows from a table into another existing table using SELECT statement

 

INSERT INTO new_table_name (column,..)
SELECT (column,..) FROM old_table_name;

Example

Insert into students_copy select * from students;

select * from students_copy;

Creating new table form multiple tables

CREATE TABLE new_table_name

  AS (SELECT column_1, column2, ... column_n

      FROM old_table_name, old_table_name_2, .. old_table_name_n where condition);

Example

Let us first have a look at students and marks table for reference:

select * from marks

select * from students;

create table persons as select firstname,lastname,rollno,english from students,marks;

We received above error since both the students and marks table have rollno column in common so we need to specify the table name in prefix to refer. Also if we don’t mention the where clause then it will create 6*6=36 rows in persons table. Modified query is as shown below:

create table persons as select students.firstname,students.lastname,students.rollno,marks.english from students,marks

where students.rollno=marks.rollno;

select * from persons;