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.

Syntax

CREATE {TABLE | CLASS} <table_name>

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

                   [REPLACE] AS <select_statement>

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;

 

           id  phone

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

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

--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;

 

           id  phone

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

            1  '111-1111'

            2  '222-2222'

            3  '333-3333'

 

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

 

  name                           id  phone

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

  NULL                            1  '111-1111'

  NULL                            2  '222-2222'

  NULL                            3  '333-3333'

 

--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;

 

          id1          id2

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

            1            1

            1            2

            1            3

            2            1

            2            2

            2            3

            3            1

            3            2

            3            3

 

--REPLACE is used on the UNIQUE column

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

SELECT * FROM new_tbl5;

 

          id1          id2

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

            1            3

            2            3

            3            3