Posts Tagged ‘SQL Syntax’

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.

SQL Syntax:

The basic syntax for creating a table from another table is as follows:

CREATE TABLE NEW_TABLE_NAME AS
   SELECT
[ column1, column2columnN ]
   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.

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

 

Designed for Mytoptutorials.com