Oracle CREATE TABLE AS operator

Oracle/PLSQL CREATE TABLE AS operator can be used to create a table from an existing table by copying columns of an existing table.

It is important to note that when creating a table in this way, the new table will be filled with records from an existing table (based on the SELECT operator).

Create table – by copying all columns from another table

CREATE TABLE AS syntax in Oracle PL/SQL, to copy all columns:

CREATE TABLE new_table
AS (SELECT * FROM old_table);

Consider the example of CREATE TABLE AS, which will create a table by copying all columns from another table. For example:

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

This example will create a new supplier table that includes all columns from the companies table.
If there are no entries in the companies table, the new supplier table will be filled with the entries returned by the SELECT operator.

Create table – by copying the selected columns from another table

CREATE TABLE AS syntax, to copy selected columns to Oracle PL/SQL:

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

Consider the example of CREATE TABLE AS, which shows how to create a table by 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 example will create a new table with the name. The new table will only include the specified columns (company_id, address, city, state and zip) from the companies table.
In addition, if there are entries in the companies table, the new suppliers table will be filled with entries returned by the SELECT operator.

Create table – by copying selected columns from several tables

CREATE TABLE AS syntax, to copy columns from multiple tables to Oracle PL/SQL:

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

Consider the CREATE TABLE AS example, which shows how to create a table by copying selected columns from multiple 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);

In this example, a new table with the name suppliers will be created based on the columns defined from the two tables companies and categories (company_id, address, category_type).

Frequently asked questions

Q: How can I create a table in Oracle/PLSQL from another table without copying any data from an old table?

A: In order to do that, you need to apply CREATE TABLE AS with the following syntax in Oracle/PLSQL:

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

For example:

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

This will create a new table with the name suppliers, which includes all columns from the companies table, but without any data from the companies table.

Oracle Database tutorials: How to CREATE TABLE using sql developer and command prompt