SQL CREATE

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;

 

Leave a Reply