---
metadata:
  - name: generator
    content: Diplodoc Platform v5.52.0
alternate:
  - https://ydb.tech/docs/en/yql/reference/syntax/create_table/as_select.md?version=v26.1
  - https://ydb.tech/docs/ru/yql/reference/syntax/create_table/as_select.md?version=v26.1
  - href: en/yql/reference/syntax/create_table/as_select.md
    type: text/markdown
    title: Markdown version
  - href: ../../../../llms.txt
    type: text/markdown
    title: llms.txt
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=v26.1#column-oriented-tables) tables. Support for [row-oriented](https://ydb.tech/docs/en/concepts/datamodel/table.md?version=v26.1#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=v26.1) 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=v26.1) 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](https://ydb.tech/docs/en/yql/reference/syntax/create_table/secondary_index.md?version=v26.1), [vector indexes](https://ydb.tech/docs/en/yql/reference/syntax/create_table/vector_index.md?version=v26.1), or [column groups](https://ydb.tech/docs/en/yql/reference/syntax/create_table/family.md?version=v26.1). All of those can be changed after the table has been created using [`ALTER TABLE`](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/index.md?version=v26.1). [Additional parameters](https://ydb.tech/docs/en/yql/reference/syntax/create_table/with.md?version=v26.1) are also supported.



## Considerations

{% note warning %}

Rows are overwritten, similar to using [`REPLACE INTO`](https://ydb.tech/docs/en/yql/reference/syntax/replace_into.md?version=v26.1 ), 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 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](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=v26.1), [DECLARE](https://ydb.tech/docs/en/yql/reference/syntax/declare.md?version=v26.1) or [named expressions](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=v26.1#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=v26.1#tablet) doesn't cause errors.

* `CREATE TABLE AS` allows using [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=v26.1#column-oriented-table) and [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=v26.1#row-oriented-table) 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

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