Synopsis

CREATE TABLE table_name
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ 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 WITH clause can be used to set properties on the newly created table. To list all available table properties, run the following query:

SELECT * FROM system.metadata.table_properties

Examples

Create a new table orders_by_date that summarizes orders:

CREATE TABLE orders_by_date
WITH (format = 'ORC')
AS
SELECT orderdate, sum(totalprice) AS price
FROM orders
GROUP BY orderdate

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

CREATE TABLE empty_nation AS
SELECT *
FROM nation
WITH NO DATA