CREATE TABLE AS SELECT

Description

You can create a new table that contains the result records of the SELECT statement by using the CREATE TABLE...AS SELECT statement. You can define column and table constraints for the new table. The following rules are applied to reflect the result records of the SELECT statement.

• If col_1 is defined in the new table and the same column col_1 is specified in select_statement, the result record of the SELECT statement is stored as col_1 value in the new table. Type casting is attempted if the column names are identical but the columns types are different.
• If col_1 and col_2 are defined in the new table, col_1, col_2 and col_3 are specified in the column list of the select_statement and there is a containment relationship between all of them, col_1, col_2 and col_3 are created in the new table and the result data of the SELECT statement is stored as values for all columns. Type casting is attempted if the column names are identical but the columns types are different.
• If columns col_1 and col_2 are defined in the new table and col_1 and col_3 are defined in the column list of select_statement without any containment relationship between them, col_1, col_2 and col_3 are created in the new table, the result data of the SELECT statement is stored only for col_1 and col_3 which are specified in select_statement, and NULL is stored as the value of col_2.
• Column aliases can be included in the column list of select_statement. In this case, new column alias is used as a new table column name. It is recommended to use an alias because invalid column name is created, if an alias does not exist when a function calling or an expression is used.
• The REPLACE option is valid only when the UNIQUE constraint is defined in a new table column (col_1). When duplicate values exist in the result record of select_statement, a UNIQUE value is stored for col_1 if the REPLACE option has been defined, or an error message is displayed if the REPLACE option is omitted due to the violation of the UNIQUE constraint.

Syntax

CREATE {TABLE | CLASS} <table_name>

                   [( <column_definition> [,<table_constraint>]... )]

                   [REPLACE] AS <select_statement>

• table_name : A name of the table to be created.
• column_definition : Defines a column. If it is omitted, the column schema of SELECT statement is replicated; however, the constraint or the AUTO_INCREMENT attribute is not replicated.
• table_constraint : Defines table constraint.
• select_statement : A SELECT statement targeting a source table that already exists in the database.

Example

CREATE TABLE a_tbl(

id INT NOT NULL DEFAULT 0 PRIMARY KEY,

phone VARCHAR(10));

INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333');

 

--creating a table without column definition

CREATE TABLE new_tbl1 AS SELECT * FROM a_tbl;

SELECT * FROM new_tbl1;

;xr

 

=== <Result of SELECT Command in Line 1> ===

 

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

 

3 rows selected.

 

--all of column values are replicated from a_tbl

CREATE TABLE new_tbl2

(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, phone VARCHAR) AS SELECT * FROM a_tbl;

SELECT * FROM new_tbl2;

;xr

 

=== <Result of SELECT Command in Line 1> ===

 

           id  phone

===================================

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

3 rows selected.

 

--some of column values are replicated from a_tbl and the rest is NULL

CREATE TABLE new_tbl3

(id INT, name VARCHAR) AS SELECT id, phone FROM a_tbl;

SELECT * FROM new_tbl3

;xr

 

=== <Result of SELECT Command in Line 1> ===

 

  name                           id  phone

=========================================================

  NULL                            1  '111-1111'

  NULL                            2  '222-2222'

  NULL                            3  '333-3333'

 

3 rows selected.

 

--column alias in the select statement should be used in the column definition

CREATE TABLE new_tbl4

(id1 int, id2 int)AS SELECT t1.id id1, t2.id id2 FROM new_tbl1 t1, new_tbl2 t2;

SELECT * FROM new_tbl4;

;xr

 

=== <Result of SELECT Command in Line 1> ===

 

          id1          id2

==========================

            1            1

            1            2

            1            3

            2            1

            2            2

            2            3

            3            1

            3            2

            3            3

 

 

9 rows selected.

 

--REPLACE is used on the UNIQUE column

CREATE TABLE new_tbl5(id1 int UNIQUE) REPLACE AS SELECT * FROM new_tbl4;

SELECT * FROM new_tbl5;

;xr

 

=== <Result of SELECT Command in Line 1> ===

 

          id1          id2

==========================

            1            3

            2            3

            3            3

 

3 rows selected.