How To Generate Create Table Script In Toad Oracle

totn Oracle / PLSQL

Oracle / PLSQL: CREATE TABLE AS Statement

This Oracle tutorial explains how to use the Oracle CREATE TABLE AS statement with syntax and examples.

Description

You can also use the Oracle CREATE TABLE AS statement to create a table from an existing table by copying the existing table's columns.

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

Create TAble - By Copying all columns from another table

Syntax

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

CREATE TABLE new_table   AS (SELECT * FROM old_table);

Example

Let's look at a CREATE TABLE AS example that shows how to create a table by copying all columns from another table.

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

This example would create a new table called suppliers that included all columns from the companies table.

If there were records in the companies table, then the new suppliers table would be populated with the records returned by the SELECT statement.

Create TAble - By Copying selected columns from another 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 look at a CREATE TABLE AS example that 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 would create a new table called suppliers, but the new table would only include the specified columns (ie: company_id, address, city, state, and zip) from the companies table.

Again, if there were records in the companies table, then the new suppliers table would be populated with the records returned by the SELECT statement.

Create table - By Copying selected columns from multiple tables

Syntax

The syntax for the CREATE TABLE AS statement that copies columns from multiple 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 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);

This example would create a new table called suppliers based on columns definitions from both the companies 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 old 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 companies table, but no data from the companies table.

Acknowledgements: We'd like to thank Daniel W. for providing this solution!