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;

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in the IT industry for more than 12 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting, Website Development, and IAM(ForgeRock) Specialist

I am a self-learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.