How to create table as select in oracle

oracle create table as select

• A table can be created from an existing table in the database using a sub query option.
• The table is created with specified column names and rows retrieved by the select statement are inserted into the table
• if the table column specification is not given, table is created  with the same column as given in sub query
• The below Oracle create table as select script creates a new table TEST2. All  the data in TEST table is inserted into TEST2 table

CREATE TABLE TEST2
AS
SELECT * FROM TEST

• Data can also be copied based on conditions or we can put rownum <1  condition to just get the table structure
• The column data type definitions including the explicitly imposed NOT NULL constraints are copied into the new table.
• We can select the individual column instead of select *

CREATE TABLE TEST2
AS
SELECT col1, col2, col3 FROM TEST;

Here we are just creating table Test2 with just three column from Test.

• You can also specify the storage parameter like tablespace as you specified in normal create table command

CREATE TABLE TEST2 tablespace TOOLS
AS
SELECT col1, col2, col3 FROM TEST

Here we are specifying the tablespace name where new table is being created

• You can create table in Parallel and nologging for faster creation

create table EMP_SUMMARY parallel 4 nologging 
as
select /*+parallel(a 16) */ * from EMP a;

• We can also use create table as select to use the same datatype as the source table but new column name in new table

CREATE TABLE TEST (colT1,colT2) AS SELECT col1, col2 FROM TEST1;

Here we are defining the different column name colT1, colT2 in the new table

• We can also create table with no data also with just structure copied

CREATE TABLE TEST3 AS SELECT * FROM TEST WITH NO DATA;

Here we are creating the table TEST3 with same structure as TEST but without any data

• When you are creating table as select , you can create the primary key also

CREATE TABLE TEST (colT1 primary key ,colT2) AS SELECT col1, col2 FROM TEST1;

I hope you like this content on How to create table as select in oracle

Related Articles
How to update table in oracle : An update statement in oracle is used to modify the existing rows in the oracle table .The update can be executed in multiple ways
How to Truncate TABLE in Oracle: Truncate TABLE in Oracle is faster than deleting from the table in oracle. It is a DDL statement and it does not fire the on delete triggers
how to delete a row in oracle: Delete from the table in oracle is used to delete the rows. DELETE  rows can be done using EXISTS/NOT EXISTS clause,table based on a subquery, cascade
Alter Table in Oracle
INSERT statement in Oracle
create table syntax in oracle
Primary Key in Oracle