INSERT INTO Statement

Description

The INSERT INTO statement inserts data into a table. The data comes from a subselect query. It is commonly used to input prediction results into a database table.

Syntax

Here is the syntax:

INSERT INTO [integration_name].[table_name]
    (SELECT ...);

Please note that the destination table ([integration_name].[table_name]) must exist and contain all the columns where the data is to be inserted.

And the steps followed by the syntax:

• It executes a subselect query to get the output dataset.
• It uses the INSERT INTO statement to insert the output of the (SELECT ...) query into the [integration_name].[table_name] table.

On execution, we get:

Query OK, 0 row(s) updated - x.xxxs

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:

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.

INSERT INTO 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 row(s) updated - x.xxxs