Create a Table - Mindsdb

Description

The CREATE TABLE statement creates a table and optionally fills it with data from provided query. It may be used to materialize prediction results as tables.

Syntax

You can use the CREATE TABLE statement to create an empty table:

sql

CREATE TABLE integration_name.table_name (
  column_name data_type,
  ...
);

You can use the CREATE TABLE statement to create a table and fill it with data:

sql

CREATE TABLE integration_name.table_name
    (SELECT ...);

Or the CREATE OR REPLACE TABLE statement:

sql

CREATE OR REPLACE TABLE integration_name.table_name
    (SELECT ...);

Here is how to list tables from a connected data source:

sql

SHOW TABLES FROM data_source_name;

<Note> Note that the `integration_name` connection must be created with the [`CREATE DATABASE`](/mindsdb_sql/sql/create/database) statement and the user with write access. </Note>

Here are the steps followed by the syntax:

It executes a subselect query to get the output data.
In the case of the CREATE OR REPLACE TABLE statement, the integration_name.table_name table is dropped before recreating it.
It (re)creates the integration_name.table_name table inside the integration_name integration.
It uses the INSERT INTO statement to insert the output of the (SELECT ...) query into the integration_name.table_name.

Example

We want to save the prediction results into the int1.tbl1 table.

Here is the schema structure used throughout this example:

bash

int1
└── tbl1
mindsdb
└── predictor_name
int2
└── tbl2

Where:

Name	Description
`int1`	Integration where the table that stores prediction results resides.
`tbl1`	Table that stores prediction results.
`predictor_name`	Name of the model.
`int2`	Integration where the data source table used in the inner `SELECT` statement resides.
`tbl2`	Data source table used in the inner `SELECT` statement.

Let's execute the query.

sql

CREATE OR REPLACE TABLE int1.tbl1 (
    SELECT *
    FROM int2.tbl2 AS ta
    JOIN mindsdb.predictor_name AS tb
    WHERE ta.date > '2015-12-31'
);