Creating a Table from an Existing Table
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected. When you will create a new table using the existing table, the new table would be populated using the existing values in the old table.
The basic syntax for creating a table from another table is as follows −
CREATE TABLE NEW_TABLE_NAME AS SELECT [ column1, column2...columnN ] FROM EXISTING_TABLE_NAME [ WHERE ]
Here, column1, column2... are the fields of the existing table and the same would be used to create fields of the new table.
Following is an example, which would create a table SALARY using the CUSTOMERS table and having the fields customer ID and customer SALARY −
SQL> CREATE TABLE SALARY AS SELECT ID, SALARY FROM CUSTOMERS;
This would create a new table SALARY which will have the following records −
+----+----------+ | ID | SALARY | +----+----------+ | 1 | 2000.00 | | 2 | 1500.00 | | 3 | 2000.00 | | 4 | 6500.00 | | 5 | 8500.00 | | 6 | 4500.00 | | 7 | 10000.00 | +----+----------+