Create New Table by Selecting Data from Other Tables with CREATE TABLE AS

By using SQL data manipulation statement, a new, temporary, backup or regular table can be created and filled or inserted with data from another table or tables retrieved by SELECT statement. The SQL data manipulation language is CREATE TABLE AS which can be used to create a new table built from contents of result set by a query on a table that already exists within the database. Both the column types, and row data for the new table, come from the SELECT command specified by select.

CREATE TABLE AS has the following syntax or synopsis in simple form:

CREATE TABLE new_table_name [ ( column [, ...] ) ] AS SELECT [ ( column [, ...] ) ] FROM existing table_name

The SELECT statement can be in a complex form where data is been retrieved from several tables. For the new table, column names can be specified by including the column names in a comma-delimited list. Very important point to take note is that there should be the same number of columns specified in the column list preceding the AS clause (optional, if specified) for new table as the number of target columns that are returned by the select statement. If the optional list of columns in parentheses of new table contains different number of rows than the rows the SELECT statement returns, the following error message will be displayed:

ERROR: CREATE TABLE/AS SELECT has mismatched column count

In its simplest form, CREATE TABLE AS statement will create a complete backup of an existing table including all columns and data simply by using the statement CREATE TABLE new_table_name AS SELECT * FROM existing_table_name.

Share and contribute or get technical support and help at My Digital Life Forums.



This entry was posted on Wednesday, August 23rd, 2006 at 11:33 pm and is filed under Databases. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Create New Table by Selecting Data from Other Tables with CREATE TABLE AS”

  1. ikarus
    April 1st, 2007 03:26
    1

    We can do it also with the query,

    SELECT * INTO newtable FROM existingtable

    I think its a little bit shorter.

  2. tariq
    January 10th, 2008 13:31
    2

    Data in the table that is created using create table as select” is different from the data that is returned when the same select that creates the table is executed separately????
    WE are using the tool PL/SQL Developer .
    Any idea what this problem is due to?

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting

« ORA-02449 Oracle Drop Table Error
Free Gmail Account with Voicemail and Streaming MP3 Audio Player »

Custom Search

New Articles

Incoming Search Terms for the Article

create table as select - create table select - create table from select - create new table - DB2 create table as select - make a new section on myspace - create table with select - create table select - db2 create table as - create table from select statement - create table as select - new table myspace - how to make a new section on myspace - Create table using select - create table as select access - oracle create table as select - create table select access - db2 create table from select - db2 create table select - access create table as select - access create table select - new section on myspace - oracle create table select - create table as select db2 - create table from other tables - create table with data - myspace create new table - DB2 create table from another table - create new myspace table - ORACLE CREATE TABLE SELECT * FROM - create table from select * - access create table from select - create table select as - add new section on myspace - create table select db2 - access create table as - CREATE TABLE AS SELECT IN ACCESS - create table select from - create table from another table in db2 - create a table in myspace - create table as select oracle - create table from other table - make another table on myspace - myspace new table - create new section - oracle create table from select - create new table from existing table in oracle - add tables myspace - all - create table (select -