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.

The CREATE TABLE AS syntax allows you to specify only the primary key and WITH parameters, so when creating a table, specifying column names, secondary indexes, vector indexes, full-text indexes, local bloom indexes, local min_max indexes, and column groups is not supported. The column names and data types of the new table are automatically inherited from the result set of the SELECT query. You can change all of the above using ALTER TABLE after creating the table. Additionally, additional parameters are supported.

Considerations

• CREATE TABLE AS is supported only in the implicit transaction control mode. The table will appear at the specified path already populated.
• CREATE TABLE AS can only be a single 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 table in the temporary directory .tmp/sessions, and after successful data write moves it to the specified location. If the operation is interrupted due to an error, the temporary table is not deleted immediately but remains in the system for some time.

Examples

• Creating a columnar table from 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;