• It is easy to create a new table from the existing table instead of specifying a new structure.
  • It creates a new table with the column specified into the select query.
  • It will also insert records from the existing table.

Syntax:

CREATE TABLE <table name> AS SELECT <column list> FROM <table name>;

Example:

CREATE TABLE stud AS SELECT std_no, std_name FROM student;
  • It will create a new table stud with two columns std_no and std_name from the existing student table.
  • Data type and size of the new table will be the same as of the existing table.

Inserting record from the existing table:

  • Sometimes we need to insert similar records from the existing table.
  • Oracle allows inserting records from existing table either from all columns or any specific column.

Syntax:

INSERT INTO <table name> VALUES(column list) SELECT<column list> FROM <table name>;

Example:

INSERT INTO stud VALUES(std_no, std_name) SELECT std_no, std_name  FROM student;