Create a Table

​

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:

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:

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

Or the CREATE OR REPLACE TABLE statement:

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

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.

On execution, we get:

Query OK, 0 rows affected (x.xxx sec)

​

Example

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

Here is the schema structure used throughout this example:

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

Where:

Let’s execute the query.

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

On execution, we get:

Query OK, 0 rows affected (x.xxx sec)