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 select from - DB2 create table select - db2 create table as select - create table from select - create new table - db2 create table from select - create table with select - create table using select - create table as select oracle - new table myspace - create table select * from - select create table - create table select * from - create table with data - create table select - oracle select into new table - create table as select in db2 - create table as select - create table as select db2 - create table select db2 - access create table select - create table select oracle - create table select access - ms access create table from select - create table as select from - creating table in oracle using existing table - create table as select in oracle - access build a column in table from another table - create table with select statement - create backup table sql - oracle 'create table' as select - select create new table - access create table as select - create table with data in db2 - db2 select into new table - how to create new table using existing table in oracle - myspace create new table - create table + select - db2 create table from another table - new table section myspace - ms access create table select - how we create a table from another table in oracle - how to create table from select db2 - make a new table on myspace - create new table using select - select data and create table query - oracle create table select - CREATING A TABLE IN DB2 TAKING DATA FROM OTHER TABLE -