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.

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.

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

  1. Imran Aziz Khan
    September 5th, 2009 14:30
    4

    Check your select statement again and shuffle the joining conditions in where clause. No need to change but change the position. Try to equate id’s first and contants latter. Hope this will solve your problem.

  2. Dinesh
    January 28th, 2009 17:46
    3

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

    this query possible in MS-ACCESS or not

  3. 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?

  4. 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.

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 to comments feature has been disabled. To receive notification of latest comments posted, subscribe to My Digital Life Comments RSS feed or register to receive new comments in daily email digest.
« ORA-02449 Oracle Drop Table Error
Free Gmail Account with Voicemail and Streaming MP3 Audio Player »

New Articles

Incoming Search Terms for the Article

create table as select - create table from select - create table select - create table select into - create a data table - create table from another table - db2 create table as select - create a data table online - create table as select from - CREATE TABLE AS SELECT - create table with select - create table with data from another table - db2 create table from select - sqlite create table as select - db2 create table as select from - create table select from - Create table with data - oracle create table as select from - oracle create table as select - create table from select statement - how to create a table from another table - create table from a select statement - create table as select access - create new table - create table as select * - create table by select - How to create a new table from an existing table in oracle - create table using select - oracle select into new table - sql create table from another table - create data tables - create table from existing table in db2 - create table as select db2 - create table as+ with data - creating table using other table - create table in select statement - db2 create table as - select create table - sql create backup table - as access create table - create table as select from,db2 - create a table of data - db2 create new table from old table - db2 create table like select - db2 create table select from - create table into - create table as select in MS ACCESS - creating backup table in oracle - create table from existing data - how to create New table -