INSERT TABLE

Description

The INSERT statement inserts new rows into a table or overwrites the existing data in the table. The inserted rows can be specified by value expressions or result from a query.

Syntax

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

Parameters

  • table_identifier

    Specifies a table name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • partition_spec

    An optional parameter that specifies a comma-separated list of key and value pairs for partitions. Note that one can use a typed literal (e.g., date’2019-01-02’) in the partition spec.

    Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

  • column_list

    An optional parameter that specifies a comma-separated list of columns belonging to the table_identifier table. Spark will reorder the columns of the input query to match the table schema according to the specified column list.

    Note:The current behaviour has some limitations:

    • All specified columns should exist in the table and not be duplicated from each other. It includes all columns except the static partition columns.
    • The size of the column list should be exactly the size of the data from VALUES clause or query.

  • 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 separate 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:

Examples

Insert Into

Single Row Insert Using a VALUES Clause
  1. CREATE TABLE students (name VARCHAR(64), address VARCHAR(64))
  2.     USING PARQUET PARTITIONED BY (student_id INT);
  4. INSERT INTO students VALUES
  5.     ('Amy Smith', '123 Park Ave, San Jose', 111111);
  7. SELECT * FROM students;
  8. +---------+----------------------+----------+
  9. |     name|               address|student_id|
  10. +---------+----------------------+----------+
  11. |Amy Smith|123 Park Ave, San Jose|    111111|
  12. +---------+----------------------+----------+
Multi-Row Insert Using a VALUES Clause
  1. INSERT INTO students VALUES
  2.     ('Bob Brown', '456 Taylor St, Cupertino', 222222),
  3.     ('Cathy Johnson', '789 Race Ave, Palo Alto', 333333);
  5. SELECT * FROM students;
  6. +-------------+------------------------+----------+
  7. |         name|                 address|student_id|
  8. +-------------+------------------------+----------+
  9. |    Amy Smith|  123 Park Ave, San Jose|    111111|
  10. +-------------+------------------------+----------+
  11. |    Bob Brown|456 Taylor St, Cupertino|    222222|
  12. +-------------+------------------------+----------+
  13. |Cathy Johnson| 789 Race Ave, Palo Alto|    333333|
  14. +--------------+-----------------------+----------+
Insert Using a SELECT Statement
  1. -- Assuming the persons table has already been created and populated.
  2. SELECT * FROM persons;
  3. +-------------+--------------------------+---------+
  4. |         name|                   address|      ssn|
  5. +-------------+--------------------------+---------+
  6. |Dora Williams|134 Forest Ave, Menlo Park|123456789|
  7. +-------------+--------------------------+---------+
  8. |  Eddie Davis|   245 Market St, Milpitas|345678901|
  9. +-------------+--------------------------+---------+
  11. INSERT INTO students PARTITION (student_id = 444444)
  12.     SELECT name, address FROM persons WHERE name = "Dora Williams";
  14. SELECT * FROM students;
  15. +-------------+--------------------------+----------+
  16. |         name|                   address|student_id|
  17. +-------------+--------------------------+----------+
  18. |    Amy Smith|    123 Park Ave, San Jose|    111111|
  19. +-------------+--------------------------+----------+
  20. |    Bob Brown|  456 Taylor St, Cupertino|    222222|
  21. +-------------+--------------------------+----------+
  22. |Cathy Johnson|   789 Race Ave, Palo Alto|    333333|
  23. +-------------+--------------------------+----------+
  24. |Dora Williams|134 Forest Ave, Menlo Park|    444444|
  25. +-------------+--------------------------+----------+
Insert Using a TABLE Statement
  1. -- Assuming the visiting_students table has already been created and populated.
  2. SELECT * FROM visiting_students;
  3. +-------------+---------------------+----------+
  4. |         name|              address|student_id|
  5. +-------------+---------------------+----------+
  6. |Fleur Laurent|345 Copper St, London|    777777|
  7. +-------------+---------------------+----------+
  8. |Gordon Martin| 779 Lake Ave, Oxford|    888888|
  9. +-------------+---------------------+----------+
  11. INSERT INTO students TABLE visiting_students;
  13. SELECT * FROM students;
  14. +-------------+--------------------------+----------+
  15. |         name|                   address|student_id|
  16. +-------------+--------------------------+----------+
  17. |    Amy Smith|    123 Park Ave, San Jose|    111111|
  18. +-------------+--------------------------+----------+
  19. |    Bob Brown|  456 Taylor St, Cupertino|    222222|
  20. +-------------+--------------------------+----------+
  21. |Cathy Johnson|   789 Race Ave, Palo Alto|    333333|
  22. +-------------+--------------------------+----------+
  23. |Dora Williams|134 Forest Ave, Menlo Park|    444444|
  24. +-------------+--------------------------+----------+
  25. |Fleur Laurent|     345 Copper St, London|    777777|
  26. +-------------+--------------------------+----------+
  27. |Gordon Martin|      779 Lake Ave, Oxford|    888888|
  28. +-------------+--------------------------+----------+
Insert Using a FROM Statement
  1. -- Assuming the applicants table has already been created and populated.
  2. SELECT * FROM applicants;
  3. +-----------+--------------------------+----------+---------+
  4. |       name|                   address|student_id|qualified|
  5. +-----------+--------------------------+----------+---------+
  6. |Helen Davis| 469 Mission St, San Diego|    999999|     true|
  7. +-----------+--------------------------+----------+---------+
  8. |   Ivy King|367 Leigh Ave, Santa Clara|    101010|    false|
  9. +-----------+--------------------------+----------+---------+
  10. | Jason Wang|     908 Bird St, Saratoga|    121212|     true|
  11. +-----------+--------------------------+----------+---------+
  13. INSERT INTO students
  14.      FROM applicants SELECT name, address, id applicants WHERE qualified = true;
  16. SELECT * FROM students;
  17. +-------------+--------------------------+----------+
  18. |         name|                   address|student_id|
  19. +-------------+--------------------------+----------+
  20. |    Amy Smith|    123 Park Ave, San Jose|    111111|
  21. +-------------+--------------------------+----------+
  22. |    Bob Brown|  456 Taylor St, Cupertino|    222222|
  23. +-------------+--------------------------+----------+
  24. |Cathy Johnson|   789 Race Ave, Palo Alto|    333333|
  25. +-------------+--------------------------+----------+
  26. |Dora Williams|134 Forest Ave, Menlo Park|    444444|
  27. +-------------+--------------------------+----------+
  28. |Fleur Laurent|     345 Copper St, London|    777777|
  29. +-------------+--------------------------+----------+
  30. |Gordon Martin|      779 Lake Ave, Oxford|    888888|
  31. +-------------+--------------------------+----------+
  32. |  Helen Davis| 469 Mission St, San Diego|    999999|
  33. +-------------+--------------------------+----------+
  34. |   Jason Wang|     908 Bird St, Saratoga|    121212|
  35. +-------------+--------------------------+----------+
Insert Using a Typed Date Literal for a Partition Column Value
  1. CREATE TABLE students (name STRING, address  STRING) PARTITIONED BY (birthday DATE);
  3. INSERT INTO students PARTITION (birthday = date'2019-01-02')
  4.     VALUES ('Amy Smith', '123 Park Ave, San Jose');
  6. SELECT * FROM students;
  7. +-------------+-------------------------+-----------+
  8. |         name|                  address|   birthday|
  9. +-------------+-------------------------+-----------+
  10. |    Amy Smith|   123 Park Ave, San Jose| 2019-01-02|
  11. +-------------+-------------------------+-----------+

Insert with a column list

  1. INSERT INTO students (address, name, student_id) VALUES
  2.     ('Hangzhou, China', 'Kent Yao', 11215016);
  4. SELECT * FROM students WHERE name = 'Kent Yao';
  5. +---------+----------------------+----------+
  6. |     name|               address|student_id|
  7. +---------+----------------------+----------+
  8. |Kent Yao |       Hangzhou, China|  11215016|
  9. +---------+----------------------+----------+
Insert with both a partition spec and a column list
  1. INSERT INTO students PARTITION (student_id = 11215017) (address, name) VALUES
  2.     ('Hangzhou, China', 'Kent Yao Jr.');
  4. SELECT * FROM students WHERE student_id = 11215017;
  5. +------------+----------------------+----------+
  6. |        name|               address|student_id|
  7. +------------+----------------------+----------+
  8. |Kent Yao Jr.|       Hangzhou, China|  11215017|
  9. +------------+----------------------+----------+

Insert Overwrite

Insert Using a VALUES Clause
  1. -- Assuming the students table has already been created and populated.
  2. SELECT * FROM students;
  3. +-------------+--------------------------+----------+
  4. |         name|                   address|student_id|
  5. +-------------+--------------------------+----------+
  6. |    Amy Smith|    123 Park Ave, San Jose|    111111|
  7. |    Bob Brown|  456 Taylor St, Cupertino|    222222|
  8. |Cathy Johnson|   789 Race Ave, Palo Alto|    333333|
  9. |Dora Williams|134 Forest Ave, Menlo Park|    444444|
  10. |Fleur Laurent|     345 Copper St, London|    777777|
  11. |Gordon Martin|      779 Lake Ave, Oxford|    888888|
  12. |  Helen Davis| 469 Mission St, San Diego|    999999|
  13. |   Jason Wang|     908 Bird St, Saratoga|    121212|
  14. +-------------+--------------------------+----------+
  16. INSERT OVERWRITE students VALUES
  17.     ('Ashua Hill', '456 Erica Ct, Cupertino', 111111),
  18.     ('Brian Reed', '723 Kern Ave, Palo Alto', 222222);
  20. SELECT * FROM students;
  21. +----------+-----------------------+----------+
  22. |      name|                address|student_id|
  23. +----------+-----------------------+----------+
  24. |Ashua Hill|456 Erica Ct, Cupertino|    111111|
  25. |Brian Reed|723 Kern Ave, Palo Alto|    222222|
  26. +----------+-----------------------+----------+
Insert Using a SELECT Statement
  1. -- Assuming the persons table has already been created and populated.
  2. SELECT * FROM persons;
  3. +-------------+--------------------------+---------+
  4. |         name|                   address|      ssn|
  5. +-------------+--------------------------+---------+
  6. |Dora Williams|134 Forest Ave, Menlo Park|123456789|
  7. +-------------+--------------------------+---------+
  8. |  Eddie Davis|   245 Market St, Milpitas|345678901|
  9. +-------------+--------------------------+---------+
  11. INSERT OVERWRITE students PARTITION (student_id = 222222)
  12.     SELECT name, address FROM persons WHERE name = "Dora Williams";
  14. SELECT * FROM students;
  15. +-------------+--------------------------+----------+
  16. |         name|                   address|student_id|
  17. +-------------+--------------------------+----------+
  18. |   Ashua Hill|   456 Erica Ct, Cupertino|    111111|
  19. +-------------+--------------------------+----------+
  20. |Dora Williams|134 Forest Ave, Menlo Park|    222222|
  21. +-------------+--------------------------+----------+
Insert Using a TABLE Statement
  1. -- Assuming the visiting_students table has already been created and populated.
  2. SELECT * FROM visiting_students;
  3. +-------------+---------------------+----------+
  4. |         name|              address|student_id|
  5. +-------------+---------------------+----------+
  6. |Fleur Laurent|345 Copper St, London|    777777|
  7. +-------------+---------------------+----------+
  8. |Gordon Martin| 779 Lake Ave, Oxford|    888888|
  9. +-------------+---------------------+----------+
  11. INSERT OVERWRITE students TABLE visiting_students;
  13. SELECT * FROM students;
  14. +-------------+---------------------+----------+
  15. |         name|              address|student_id|
  16. +-------------+---------------------+----------+
  17. |Fleur Laurent|345 Copper St, London|    777777|
  18. +-------------+---------------------+----------+
  19. |Gordon Martin| 779 Lake Ave, Oxford|    888888|
  20. +-------------+---------------------+----------+
Insert Using a FROM Statement
  1. -- Assuming the applicants table has already been created and populated.
  2. SELECT * FROM applicants;
  3. +-----------+--------------------------+----------+---------+
  4. |       name|                   address|student_id|qualified|
  5. +-----------+--------------------------+----------+---------+
  6. |Helen Davis| 469 Mission St, San Diego|    999999|     true|
  7. +-----------+--------------------------+----------+---------+
  8. |   Ivy King|367 Leigh Ave, Santa Clara|    101010|    false|
  9. +-----------+--------------------------+----------+---------+
  10. | Jason Wang|     908 Bird St, Saratoga|    121212|     true|
  11. +-----------+--------------------------+----------+---------+
  13. INSERT OVERWRITE students
  14.     FROM applicants SELECT name, address, id applicants WHERE qualified = true;
  16. SELECT * FROM students;
  17. +-----------+-------------------------+----------+
  18. |       name|                  address|student_id|
  19. +-----------+-------------------------+----------+
  20. |Helen Davis|469 Mission St, San Diego|    999999|
  21. +-----------+-------------------------+----------+
  22. | Jason Wang|    908 Bird St, Saratoga|    121212|
  23. +-----------+-------------------------+----------+
Insert Using a Typed Date Literal for a Partition Column Value
  1. CREATE TABLE students (name STRING, address  STRING) PARTITIONED BY (birthday DATE);
  3. INSERT INTO students PARTITION (birthday = date'2019-01-02')
  4.     VALUES ('Amy Smith', '123 Park Ave, San Jose');
  6. SELECT * FROM students;
  7. +-------------+-------------------------+-----------+
  8. |         name|                  address|   birthday|
  9. +-------------+-------------------------+-----------+
  10. |    Amy Smith|   123 Park Ave, San Jose| 2019-01-02|
  11. +-------------+-------------------------+-----------+
  13. INSERT INTO students PARTITION (birthday = date'2019-01-02')
  14.     VALUES('Jason Wang', '908 Bird St, Saratoga');
  16. SELECT * FROM students;
  17. +-----------+-------------------------+-----------+
  18. |       name|                  address|   birthday|
  19. +-----------+-------------------------+-----------+
  20. | Jason Wang|    908 Bird St, Saratoga| 2019-01-02|
  21. +-----------+-------------------------+-----------+
Insert with a column list
  1. INSERT OVERWRITE students (address, name, student_id) VALUES
  2.     ('Hangzhou, China', 'Kent Yao', 11215016);
  4. SELECT * FROM students WHERE name = 'Kent Yao';
  5. +---------+----------------------+----------+
  6. |     name|               address|student_id|
  7. +---------+----------------------+----------+
  8. |Kent Yao |       Hangzhou, China|  11215016|
  9. +---------+----------------------+----------+
Insert with both a partition spec and a column list
  1. INSERT OVERWRITE students PARTITION (student_id = 11215016) (address, name) VALUES
  2.     ('Hangzhou, China', 'Kent Yao Jr.');
  4. SELECT * FROM students WHERE student_id = 11215016;
  5. +------------+----------------------+----------+
  6. |        name|               address|student_id|
  7. +------------+----------------------+----------+
  8. |Kent Yao Jr.|       Hangzhou, China|  11215016|
  9. +------------+----------------------+----------+