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 »

Custom Search

New Articles

Incoming Search Terms for the Article

create table as select - create table from select - create table as select from - create table select - create table select into - create a data table - db2 create table as select - create table from another table - create table select from - db2 create table as select from - oracle select into new table - create new table - create table as select db2 - sql create table from another table - create table from select statement - create table with data - create data table - oracle create table as select - create table with select - create table from other table - select into create table - create table as select oracle - sqlite create table as select - DB2 create table from select - create table using select - sqlite create table from select - create table as select access - create a table from another table - select create table - create backup table sql - creating table from another table - create table from another table in DB2 - create table select statement - create table with select statement - ms access create table as select - how to create table from another table - create table insert into - how to create a table from another table - insert into select create table - Create Data Tables - create table select from table - create table into - create table select as - create table from another table db2 - create table from select db2 - create a table using select statement - db2 create table as with data - create table using select statement - create table as select from table - sql cREATE BACKUP TABLE -