Using CREATE TABLE AS Statement in Oracle

This article is written about how to use the Oracle CREATE TABLE AS statement with syntax and examples.

Description

You can also use the Oracle CREATE TABLE AS assertion to create a table from an existing table by using copying the present table’s columns.

It is important to note that when creating a table in this way, the new desk will be populated with the archives from the existing table (based on the SELECT Statement).

Create TAble – By Copying all columns from some other table

Syntax

The syntax for the CREATE TABLE AS assertion that copies all of the columns in Oracle/PLSQL is:

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

Example

Let’s seem at a CREATE TABLE AS example that suggests how to create a desk by using copying all columns from every other table.

CREATE TABLE suppliers
AS (SELECT *
    FROM companies
    WHERE company_id < 5000);

This instance would create a new desk referred to as suppliers that protected all columns from the groups table.

If there had been information in the groups table, then the new suppliers desk would be populated with the files returned by means of the SELECT statement.

Create TAble – By Copying selected columns from every other table

Syntax

The syntax for the CREATE TABLE AS statement that copies the selected columns in Oracle/PLSQL is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table);

Example

Let’s seem to be at a CREATE TABLE AS example that shows how to create a table via copying selected columns from another table.

For Example:

CREATE TABLE suppliers
  AS (SELECT company_id, address, city, state, zip
      FROM companies
      WHERE company_id < 5000);

This instance would create a new desk called suppliers, however the new table would only encompass the detailed columns (ie: company_id, address, city, state, and zip) from the corporations table.

Again, if there had been information in the groups table, then the new suppliers desk would be populated with the files back by using the SELECT statement.

Create desk – By Copying selected columns from a couple of tables

Syntax

The syntax for the CREATE TABLE AS announcement that copies columns from a couple of tables in Oracle/PLSQL is:

CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n);

Example

Let’s look at a CREATE TABLE AS example that suggests how to create a desk by means of copying selected columns from a couple of tables.

For example:

CREATE TABLE suppliers
  AS (SELECT companies.company_id, companies.address, categories.category_type
      FROM companies, categories
      WHERE companies.company_id = categories.category_id
      AND companies.company_id < 5000);

This instance would create a new desk referred to as suppliers primarily based on columns definitions from each the organizations and categories tables (ie: company_id, address, and category_type).

Frequently Asked Questions

Question: How can I create an Oracle table from another table without copying any values from the ancient table?

Answer: To do this, the Oracle CREATE TABLE syntax is:

CREATE TABLE new_table
  AS (SELECT *
      FROM old_table WHERE 1=2);

For example:

CREATE TABLE suppliers
  AS (SELECT *
      FROM companies WHERE 1=2);

This would create a new table called suppliers that included all column definitions from the corporations table, but no facts from the groups table.

Acknowledgements: We’d like to thank Daniel W. for imparting this solution!