SQL CREATE TABLE FROM ANOTHER TABLE

We can create tables two ways they are

1) Using CREATE TABLE by specifying columns names, columns data types, columns data length. Syntax:

CREATE TABLE <TableName> ( <ColumnName1> <DataType>(Length),
<ColumnName2> <DataType>(Length));

Example

CREATE TABLE EMPLOYEE (NAME Varchar2(20), ID Number(10));

2) Using CREATE TABLE statement for target table and SELECT statement for source table. Syntax

CREATE TABLE <TargeTableName> AS SELECT  * FROM <SourceTableName>

Example

CREATE TABLE EMPLOYEE_HISTORY AS SELECT * FROM EMPLOYEE

The above query will create table EMPLOYEE_HISTORY as it is of EMPLOYEE like same number of columns, same data types, same length and same data. Finally we can say EMPLOYEE_HISTORY is a replica of EMPLOYEE table.

Create table from another table with specific columns

Syntax:

CREATE TABLE <TargetTableNanme>(<ColumnName1>,<ColumnName2>) AS 
SELECT <ColumnName1>,<ColumnName2> FROM <SourceTableName>

Example

CREATE TABLE EMPLOYEE_HISTORY (FIRSTNAME, LASTNAME) AS SELECT NAME, SURNAME FROM EMPLOYEE

Create table from a another table without data

Syntax:

CREATE TABLE <TargetTableNanme>(<ColumnName1>,<ColumnName2>) AS 
SELECT <ColumnName1>,<ColumnName2> FROM <SourceTableName> WHERE 1=2;

Example

CREATE TABLE EMPLOYEE_HISTORY (FIRSTNAME, LASTNAME) AS 
SELECT NAME, SURNAME FROM EMPLOYEE WHERE 1=2;