Create a Table from another Table in Oracle SQL

In my previous article, you have seen how to create a Table and Global Temporary Table in Oracle. In this article, you will learn how to create a table from another table.

create a table

1) How to create a Table from an Existing Table?

2) How to create a Table from an Existing Table with filtered data?

3) How to create a Table from an Existing Table with few columns?


4) How to create a Table from two or more than two Tables?

5) How to create a Table structure from an Existing Table? (Without any data)


Note: When you create a new table from an existing table, the new table is populated with all the columns and data from an existing table.

1) Create a Table from an Existing Table:

Syntax: You can create a table from an existing table using below syntax:

CREATE TABLE new-table-name 
   AS (SELECT * FROM existing-table-name);

Example:

CREATE TABLE TB_EMP_DETAILS 
AS (SELECT * FROM TB_EMPLOYEE);

This example would create a new table called TB_EMP_DETAILS that would include all the columns and records from an existing table called TB_EMPLOYEE.

2) Create a Table from an Existing Table with filtered data:

Syntax: You can create a table from an existing table with filter data using below syntax:

CREATE TABLE new-table-name 
   AS (SELECT * FROM existing-table-name 
       WHERE column-name > values);

Example:

CREATE TABLE TB_EMP_DETAILS 
   AS (SELECT * FROM TB_EMPLOYEE
       WHERE EMP_SALARY>20000);

This example would create a new table called TB_EMP_DETAILS that would include all the columns and records of those employees whose salary is more than 20000 from an existing table called TB_EMPLOYEE.

3) Create a Table from an Existing Table with few/selected columns:

Syntax: You can create a Table with few/selected columns from an existing Table using below syntax.

CREATE TABLE new-table-name
  AS (SELECT column-1, column-2, ... column-n FROM old-table-name);

Example:

CREATE TABLE TB_EMP_DETAILS
  AS (SELECT EMP_ID, EMP_NAME, EMP_CITY FROM TB_EMPLOYEE);

This example would create a new table called TB_EMP_DETAILS that would include only selected columns and all the records from an existing table called TB_EMPLOYEE.

4) Create a Table from two or more than two Tables:

Syntax: You can create a Table from two or more than two tables from an existing Table using below syntax.

CREATE TABLE new-table-name
  AS (SELECT a.column-1, a.column-2, ... a.column-n,
             b.column-1,b.column-2, ... b.column-n 
             FROM old-table1 a, old-table2 b
             WHERE a.column-1=b.column-1);

Example:

CREATE TABLE TB_EMP_DETAILS
  AS (SELECT a.EMP_ID, a.EMP_NAME, a.EMP_CITY
             b.DEPT_NAME, b.DEPT_LOCATION
             FROM TB_EMPLOYEE a, TB_DEPT b
             WHERE a.EMP_ID=b.DEPT_ID);

This example would create a new table called TB_EMP_DETAILS that would include columns and all the records from the existing tables in FROM clause called TB_EMPLOYEE, TB_DEPT.

5) Create a Table structure from an Existing Table:

Syntax: You can create a table structure from an existing table using below syntax:

CREATE TABLE new-table-name
  AS (SELECT * FROM old-table-name WHERE 1=2);

Example:

CREATE TABLE TB_EMP_DETAILS
  AS (SELECT * FROM TB_EMPLOYEE WHERE 1=2);

This example would create a new table called TB_EMP_DETAILS that would include only columns without any data from an existing table called TB_EMPLOYEE.

SiteGround Black Friday Sale Below Banner

Get Free Blogging Tips & Technology updates in your Email !!!

Subscribe via RSS feed

The following two tabs change content below.
Anil Kumar
IT Blogger Tips focus on Blogging Tips, SEO Tips, Social Media, SQL Tips, PL/SQL Tips, Oracle DBA, Linux/Unix, Latest Technology, How Tos and Technical Solutions. You can find us on Facebook | Twitter |
Find on Google+

  • Divya

    Very Nice Thoughts. Thanks For Sharing with us.

Siteground SALE
70% OFF SiteGround WordPress Hosting – $2.95/month (Exclusive Offer !!!)