martyna-mindsdb
91 lines
2.6 KiB
Plaintext
91 lines
2.6 KiB
Plaintext
---
|
|
title: Create a Table
|
|
sidebarTitle: 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:
|
|
|
|
```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`](/sql/api/insert/) 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'
|
|
);
|
|
```
|