CREATE TABLE AS Statement

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

In some cases, we can create an exact replica of a table or one that meets certain conditions.

In such cases, only the NOT NULL constraint is passed to the new table. Other constraints are not passed.

We can create a table from a statement as follows.

SQL> CREATE TABLE DBA_TABLES_BCK AS SELECT * FROM DBA_TABLES;
	
Table created.
	
Elapsed: 00:00:00.89

SQL> CREATE TABLE DBA_TABLES_BCK AS SELECT * FROM DBA_TABLES;

Table created.

Elapsed: 00:00:00.89

If it is structurally the same as a table without data, we can create it as follows.

SQL> CREATE TABLE DBA_TABLES_BCK_2 AS SELECT * FROM DBA_TABLES WHERE 1=2;
	
Table created.
	
Elapsed: 00:00:00.55

SQL> CREATE TABLE DBA_TABLES_BCK_2 AS SELECT * FROM DBA_TABLES WHERE 1=2;

Table created.

Elapsed: 00:00:00.55

If table names are given differently and data types are not specified, it takes the data types from the selected table.

SQL> create table teeest (ow,syn,tbl,nam,lnk) as select * from dba_synonyms;
	
Table created.

SQL> create table teeest (ow,syn,tbl,nam,lnk) as select * from dba_synonyms;

Table created.

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux