8.9. CREATE TABLE AS

Synopsis

  1. CREATE TABLE [ IF NOT EXISTS ] table_name [ ( column_alias, ... ) ]
  2. [ COMMENT table_comment ]
  3. [ WITH ( property_name = expression [, ...] ) ]
  4. AS query
  5. [ WITH [ NO ] DATA ]

Description

Create a new table containing the result of a SELECT query.Use CREATE TABLE to create an empty table.

The optional IF NOT EXISTS clause causes the error to besuppressed if the table already exists.

The optional WITH clause can be used to set propertieson the newly created table. To list all available tableproperties, run the following query:

  1. SELECT * FROM system.metadata.table_properties

Examples

Create a new table orders_column_aliased with the results of a query and the given column names:

  1. CREATE TABLE orders_column_aliased (order_date, total_price)
  2. AS
  3. SELECT orderdate, totalprice
  4. FROM orders

Create a new table orders_by_date that summarizes orders:

  1. CREATE TABLE orders_by_date
  2. COMMENT 'Summary of orders by date'
  3. WITH (format = 'ORC')
  4. AS
  5. SELECT orderdate, sum(totalprice) AS price
  6. FROM orders
  7. GROUP BY orderdate

Create the table orders_by_date if it does not already exist:

  1. CREATE TABLE IF NOT EXISTS orders_by_date AS
  2. SELECT orderdate, sum(totalprice) AS price
  3. FROM orders
  4. GROUP BY orderdate

Create a new empty_nation table with the same schema as nation and no data:

  1. CREATE TABLE empty_nation AS
  2. SELECT *
  3. FROM nation
  4. WITH NO DATA

See Also

CREATE TABLE, SELECT