Creating a table filled with query results

CREATE TABLE AS creates a new table table filled with data from query results.

CREATE TABLE table_name (
    PRIMARY KEY ( column, ... )
)
WITH ( key = value, ... )
AS SELECT ...

Names and types of columns will correspond to the SELECT results.
Non-optional columns will also have the NOT NULL constraint.

When creating a table using CREATE TABLE AS, it is not possible to specify column names (column names of the created table will be derived from the query result), secondary indexes, vector indexes, or column groups. All of those can be changed after the table has been created using ALTER TABLE. Additional parameters are also supported.

Considerations

• CREATE TABLE AS is supported only for [implicit transaction control mode](../../../../ concepts/transactions.md#implicit). When the table appears at the specified path it's already filled.

• CREATE TABLE AS can only be a sigle DML/DDL statement in a query. It's possible to use PRAGMA, DECLARE or named expressions in the same query.

• CREATE TABLE AS doesn't cause lock conflicts with other transactions. It doesn't use locks. Reads use a consistent snapshot. Moving or splitting tablets doesn't cause errors.

• CREATE TABLE AS allows using column-oriented tables and row-oriented tables in the same query.

• CREATE TABLE AS creates a temporary table and moves it to the specified location after filling that table. If there was an error during the CREATE TABLE AS execution, it's possible that the temporary table will not be deleted immediately, but it will remain for some short period of time.

Examples

• Creating a column-oriented table from the query results

  CREATE TABLE my_table (
      PRIMARY KEY (key1, key2)
  ) WITH (
      STORE=COLUMN
  ) AS SELECT 
      key AS key1,
      Unwrap(other_key) AS key2,
      value,
      String::Contains(value, "test") AS has_test
  FROM other_table;