Insert Into

The INSERT INTO statement inserts new rows into a table. The inserted rows can be specified by value expressions or result from a query.

The table must not be a view or an external table. In order to truncate multiple partitions at once, the user can specify the partitions in partition_spec. If no partition_spec is specified it will remove all partitions in the table.

Syntax

INSERT INTO [ TABLE ] table_identifier [ partition_spec ]
    { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }

info

When you INSERT INTO a table schema enforcement and evolution is supported. If a column’s data type cannot be safely cast to a table’s data type, a runtime exception is thrown. If schema evolution is enabled, new columns can exist as the last columns of your schema (or nested columns) for the schema to evolve.

Parameters

• table_identifier

  • [database_name.] table_name: A table name, optionally qualified with a database name. Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

• VALUES ( { value | NULL } [ , … ] ) [ , ( … ) ] The values to be inserted. Either an explicitly specified value or a NULL. Use a comma to separate each value in the clause. You can specify more than one set of values to insert multiple rows.

• query

  • A SELECT statement
  • A TABLE statement
  • A FROM statement

---

Examples

Single row insert using a VALUES clause

Here's the shortest and easiest way to insert data into a table. You only have to specify the values, but you have to pass all values in order. If you have 10 columns, you have to specify 10 values.

-- CREATE TABLE session (id INT, start_date TIMESTAMP, end_date TIMESTAMP, category INT) using delta;
-- assuming the sessions table has only four columns:
-- id, start_date, and end_date, and category, in that order
INSERT INTO sessions
VALUES (1, '2020-04-02 14:05:15.400', '2020-04-03 14:25:15.400', 1);

+-----+------------------------+--------------------------+-----------+
| id  |       start_date       |         end_date         | category  |
+-----+------------------------+--------------------------+-----------+
| 1   | 2020-04-02 14:05:15.4  | 2020-04-03 14:25:15.4    | 1         |
+-----+------------------------+--------------------------+-----------+

To skip some columns use NULL in-place of that column:

INSERT INTO sessions
VALUES (2, '2020-04-02 14:05:15.400', '2020-04-04 16:57:53.653', NULL );

+-----+------------------------+--------------------------+-----------+
| id  |       start_date       |         end_date         | category  |
+-----+------------------------+--------------------------+-----------+
| 2   | 2020-04-02 14:05:15.4  | 2020-04-04 16:57:53.653  | NULL      |
+-----+------------------------+--------------------------+-----------+

---

Multi-row insert using a VALUES clause

You can insert multiple rows in one INSERT statement by having multiple sets of values enclosed in parentheses:

INSERT INTO sessions VALUES
(1, '2020-04-02 14:05:15.400', '2020-04-03 14:25:15.400', 1),
(2, '2020-04-02 14:05:15.400', '2020-04-04 16:57:53.653', NULL ),
(3, '2020-04-02 14:05:15.400', '2020-04-04 16:57:53.653', 3 );

+-----+------------------------+--------------------------+-----------+
| id  |       start_date       |         end_date         | category  |
+-----+------------------------+--------------------------+-----------+
| 1   | 2020-04-02 14:05:15.4  | 2020-04-03 14:25:15.4    | 1         |
| 2   | 2020-04-02 14:05:15.4  | 2020-04-04 16:57:53.653  | NULL      |
| 3   | 2020-04-02 14:05:15.4  | 2020-04-04 16:57:53.653  | 3         |
+-----+------------------------+--------------------------+-----------+

---

Multi-row insert using a SELECT statement

You can insert multiple rows in one INSERT statement by selecting data from a different table. This is similar to a CREATE TABLE AS syntax :

CREATE TABLE sessions_dm (id INT, start_date TIMESTAMP, end_date TIMESTAMP, category INT) using delta;


INSERT INTO sessions_dm
SELECT *
FROM sessions
WHERE category IS NOT NULL;

-- if you want to append whole sessions table into sessions_dm table. You can use:
-- INSERT INTO sessions_dm TABLE category;


DESC TABLE sessions_dm;

+-------------+------------+----------+
|  col_name   | data_type  | comment  |
+-------------+------------+----------+
| id          | int        | NULL     |
| start_date  | timestamp  | NULL     |
| end_date    | timestamp  | NULL     |
| category    | int        | NULL     |
+-------------+------------+----------+

SELECT * FROM sessions_dm;

+-----+------------------------+--------------------------+-----------+
| id  |       start_date       |         end_date         | category  |
+-----+------------------------+--------------------------+-----------+
| 1   | 2020-04-02 14:05:15.4  | 2020-04-03 14:25:15.4    | 1         |
| 3   | 2020-04-02 14:05:15.4  | 2020-04-04 16:57:53.653  | 3         |
+-----+------------------------+--------------------------+-----------+

To append the whole sessions table into sessions_dm table. You can use:

-- appends sessions data to sessions_dm
INSERT INTO sessions_dm TABLE sessions;

Or using a FROM statement

-- appends selected data to sessions_dm
INSERT INTO sessions_dm
     FROM sessions
     SELECT id, start_date, end_date, category WHERE category IS NOT NULL;

You can also use CREATE TABLE AS with a SELECT command to copy data from an existing table:

CREATE TABLE sessions_dm AS
SELECT *
FROM sessions
WHERE category IS NOT NULL ;

DESC TABLE sessions_dm;

+-------------+------------+----------+
|  col_name   | data_type  | comment  |
+-------------+------------+----------+
| id          | int        | NULL     |
| start_date  | timestamp  | NULL     |
| end_date    | timestamp  | NULL     |
| category    | int        | NULL     |
+-------------+------------+----------+

SELECT * FROM sessions_dm;

+-----+------------------------+--------------------------+-----------+
| id  |       start_date       |         end_date         | category  |
+-----+------------------------+--------------------------+-----------+
| 1   | 2020-04-02 14:05:15.4  | 2020-04-03 14:25:15.4    | 1         |
| 3   | 2020-04-02 14:05:15.4  | 2020-04-04 16:57:53.653  | 3         |
+-----+------------------------+--------------------------+-----------+

---

Insert with a column list

INSERT INTO students (address, name, student_id) VALUES
    ('Hangzhou, China', 'Kent Yao', 11215016);

SELECT * FROM students WHERE name = 'Kent Yao';
+---------+----------------------+----------+
|     name|               address|student_id|
+---------+----------------------+----------+
|Kent Yao |       Hangzhou, China|  11215016|
+---------+----------------------+----------+

---

Insert with both a partition spec and a column list

INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES
    ('Hangzhou, China', 'Kent Yao Jr.');

SELECT * FROM students WHERE student_id = 11215017;
+------------+----------------------+----------+
|        name|               address|student_id|
+------------+----------------------+----------+
|Kent Yao Jr.|       Hangzhou, China|  11215017|
+------------+----------------------+----------+