---
metadata:
  - name: generator
    content: Diplodoc Platform v5.50.6
alternate:
  - https://ydb.tech/docs/en/yql/reference/syntax/create_table/as_select.md?version=main
  - https://ydb.tech/docs/ru/yql/reference/syntax/create_table/as_select.md?version=main
sourcePath: en/core/yql/reference/syntax/create_table/as_select.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Creating a table filled with query results

<!-- source: en/_includes/not_allow_for_oltp_note.md -->
{% note warning %}

<!-- source: en/_includes/not_allow_for_oltp_text.md -->
Supported only for [column-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#column-oriented-tables) tables. Support for [row-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main#row-oriented-tables) tables is currently under development.
<!-- endsource: en/_includes/not_allow_for_oltp_text.md -->

{% endnote %}
<!-- endsource: en/_includes/not_allow_for_oltp_note.md -->

`CREATE TABLE AS` creates a new table [table](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=main) filled with data from query results.


```yql
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](https://ydb.tech/docs/en/yql/reference/types/optional.md?version=main) 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](https://ydb.tech/docs/en/yql/reference/syntax/create_table/secondary_index.md?version=main), [vector indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/vector_index.md?version=main), [full-text indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/fulltext_index.md?version=main), [local bloom indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main), [local min_max indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/min_max_index.md?version=main), and [column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=main) 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`](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/index.md?version=main) after creating the table. Additionally, [additional parameters](https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=main) are supported.

## Considerations

{% note warning %}

Rows are overwritten, similar to using [`REPLACE INTO`](https://ydb.tech/docs/en/yql/reference/syntax/replace_into.md?version=main), but the order in which rows are written is unpredictable.

If `SELECT` returns two or more rows with the same primary key value, after the `CREATE TABLE AS` is executed, there will only be one row with that primary key value in the created table. Which record from the `SELECT` was written to the table is undetermined.

{% endnote %}

* `CREATE TABLE AS` is supported only in the [implicit transaction control](https://ydb.tech/docs/en/concepts/transactions.md?version=main#implicit) mode. The table will appear at the specified path already populated.
* `CREATE TABLE AS` can only be a single [DML](https://en.wikipedia.org/wiki/Data_manipulation_language)/[DDL](https://en.wikipedia.org/wiki/Data_definition_language) statement in a query. It's possible to use [PRAGMA](https://ydb.tech/docs/en/yql/reference/syntax/pragma.md?version=main), [DECLARE](https://ydb.tech/docs/en/yql/reference/syntax/declare.md?version=main) or [named expressions](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#named-nodes) 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](https://ydb.tech/docs/en/concepts/glossary.md?version=main#tablet) doesn't cause errors.
* `CREATE TABLE AS` allows using [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) and [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table) 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


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