SQL - Creating a Table from an Existing Table

Advertisements


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.

Syntax:

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.

Example:

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 |
+----+----------+


Advertisements
Advertisements