INSERT INTO

Description

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

Syntax

INSERT INTO [ TABLE ] table_name
    [ PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] ) ]
    { { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] } | query }

Parameters

table_name: The name of an existing table.

PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] ): Specifies one or more partition column and value pairs. The partition value is optional.

VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ]: Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted. A comma must be used to seperate each value in the clause. More than one set of values can be specified to insert multiple rows.

query

A query that produces the rows to be inserted. It can be in one of following formats:

a SELECT statement
a TABLE statement
a FROM statement

Examples

Single Row Insert Using a VALUES Clause

 CREATE TABLE students (name VARCHAR(64), address VARCHAR(64), student_id INT)
     USING PARQUET PARTITIONED BY (student_id);

 INSERT INTO students
     VALUES ('Amy Smith', '123 Park Ave, San Jose', 111111);

 SELECT * FROM students;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | student_id     |
     + -------------- + ------------------------------ + -------------- +
     | Amy Smith      | 123 Park Ave, San Jose         | 111111         |
     + -------------- + ------------------------------ + -------------- +

Multi-Row Insert Using a VALUES Clause

 INSERT INTO students
     VALUES ('Bob Brown', '456 Taylor St, Cupertino', 222222),
            ('Cathy Johnson', '789 Race Ave, Palo Alto', 333333);

 SELECT * FROM students;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | student_id     |
     + -------------- + ------------------------------ + -------------- +
     | Amy Smith      | 123 Park Ave, San Jose         | 111111         |
     + -------------- + ------------------------------ + -------------- +
     | Bob Brown      | 456 Taylor St, Cupertino       | 222222         |
     + -------------- + ------------------------------ + -------------- +
     | Cathy Johnson  | 789 Race Ave, Palo Alto        | 333333         |
     + -------------- + ------------------------------ + -------------- +

Insert Using a SELECT Statement

 -- Assuming the persons table has already been created and populated.
 SELECT * FROM persons;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | ssn            |
     + -------------- + ------------------------------ + -------------- +
     | Dora Williams  | 134 Forest Ave, Melo Park      | 123456789      |
     + -------------- + ------------------------------ + -------------- +
     | Eddie Davis    | 245 Market St, Milpitas        | 345678901      |
     + -------------- + ------------------------------ + ---------------+

 INSERT INTO students PARTITION (student_id = 444444)
     SELECT name, address FROM persons WHERE name = "Dora Williams";

 SELECT * FROM students;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | student_id     |
     + -------------- + ------------------------------ + -------------- +
     | Amy Smith      | 123 Park Ave, San Jose         | 111111         |
     + -------------- + ------------------------------ + -------------- +
     | Bob Brown      | 456 Taylor St, Cupertino       | 222222         |
     + -------------- + ------------------------------ + -------------- +
     | Cathy Johnson  | 789 Race Ave, Palo Alto        | 333333         |
     + -------------- + ------------------------------ + -------------- +
     | Dora Williams  | 134 Forest Ave, Melo Park      | 444444         |
     + -------------- + ------------------------------ + -------------- +

Insert Using a TABLE Statement

 -- Assuming the visiting_students table has already been created and populated.
 SELECT * FROM visiting_students;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | student_id     |
     + -------------- + ------------------------------ + -------------- +
     | Fleur Laurent  | 345 Copper St, London          | 777777         |
     + -------------- + ------------------------------ + -------------- +
     | Gordon Martin  | 779 Lake Ave, Oxford           | 888888         |
     + -------------- + ------------------------------ + -------------- +

 INSERT INTO students TABLE visiting_students;

 SELECT * FROM students;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | student_id     |
     + -------------- + ------------------------------ + -------------- +
     | Amy Smith      | 123 Park Ave, San Jose         | 111111         |
     + -------------- + ------------------------------ + -------------- +
     | Bob Brown      | 456 Taylor St, Cupertino       | 222222         |
     + -------------- + ------------------------------ + -------------- +
     | Cathy Johnson  | 789 Race Ave, Palo Alto        | 333333         |
     + -------------- + ------------------------------ + -------------- +
     | Dora Williams  | 134 Forest Ave, Melo Park      | 444444         |
     + -------------- + ------------------------------ + -------------- +
     | Fleur Laurent  | 345 Copper St, London          | 777777         |
     + -------------- + ------------------------------ + -------------- +
     | Gordon Martin  | 779 Lake Ave, Oxford           | 888888         |
     + -------------- + ------------------------------ + -------------- +

Insert Using a FROM Statement

 -- Assuming the applicants table has already been created and populated.
 SELECT * FROM applicants;

     + -------------- + ------------------------------ + -------------- + -------------- +
     | name           | address                        | student_id     | qualified      |
     + -------------- + ------------------------------ + -------------- + -------------- +
     | Helen Davis    | 469 Mission St, San Diego      | 999999         | true           |
     + -------------- + ------------------------------ + -------------- + -------------- +
     | Ivy King       | 367 Leigh Ave, Santa Clara     | 101010         | false          |
     + -------------- + ------------------------------ + -------------- + -------------- +
     | Jason Wang     | 908 Bird St, Saratoga          | 121212         | true           |
     + -------------- + ------------------------------ + -------------- + -------------- +

 INSERT INTO students
      FROM applicants SELECT name, address, id applicants WHERE qualified = true;

 SELECT * FROM students;

     + -------------- + ------------------------------ + -------------- +
     | name           | address                        | student_id     |
     + -------------- + ------------------------------ + -------------- +
     | Amy Smith      | 123 Park Ave, San Jose         | 111111         |
     + -------------- + ------------------------------ + -------------- +
     | Bob Brown      | 456 Taylor St, Cupertino       | 222222         |
     + -------------- + ------------------------------ + -------------- +
     | Cathy Johnson  | 789 Race Ave, Palo Alto        | 333333         |
     + -------------- + ------------------------------ + -------------- +
     | Dora Williams  | 134 Forest Ave, Melo Park      | 444444         |
     + -------------- + ------------------------------ + -------------- +
     | Fleur Laurent  | 345 Copper St, London          | 777777         |
     + -------------- + ------------------------------ + -------------- +
     | Gordon Martin  | 779 Lake Ave, Oxford           | 888888         |
     + -------------- + ------------------------------ + -------------- +
     | Helen Davis    | 469 Mission St, San Diego      | 999999         |
     + -------------- + ------------------------------ + -------------- +
     | Jason Wang     | 908 Bird St, Saratoga          | 121212         |
     + -------------- + ------------------------------ + -------------- +

Spark SQL Guide