• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Sql » How to create table as select in oracle

How to create table as select in oracle

December 4, 2020 by techgoeasy Leave a Comment

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
oracle create table as select
How to create table as select in oracle
  • 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

Filed Under: Oracle, Oracle Sql

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • Password Version in oracle
  • How to login as user without changing the password in Oracle database(alter user identified by values)
  • How to check encrypted tablespace in the Database
  • How To Export -Import TDE Master Encryption Key
  • How to Configure Auto Login TDE Wallet

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us