SQL - 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 create a new table using existing table, new table would be populated using 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 existing table and same would be used to create fields of new table.
Following is an example, which would create a table SALARY using CUSTOMERS table and having fields customer ID and customer SALARY:
SQL> CREATE TABLE SALARY AS SELECT ID, SALARY FROM CUSTOMERS;
This would create new table SALARY, which would 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 | +----+----------+