Narayana Tutorial

Online Java Tutorial Blog

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;

 

Narayanaswamy

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

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

Leave a Reply

Narayana Tutorial © 2018 Frontier Theme
Show Button
Hide Button