INSERT OVERWRITE

2020-01-21

Description

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

Syntax

INSERT OVERWRITE [ TABLE ] table_identifier [ partition_spec [ IF NOT EXISTS ] ] { { 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.

Syntax: PARTITION ( partition_col_name [ = partition_col_val ] [ , ... ] )
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:
  • a SELECT statement
  • a TABLE statement
  • a FROM statement

Examples

Insert Using a VALUES Clause

-- Assuming the students table has already been created and populated. 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         |
 + -------------- + ------------------------------ + -------------- +

INSERT OVERWRITE students VALUES ('Ashua Hill', '456 Erica Ct, Cupertino', 111111), ('Brian Reed', '723 Kern Ave, Palo Alto', 222222);

SELECT * FROM students;

 + -------------- + ------------------------------ + -------------- +
 | name           | address                        | student_id     |
 + -------------- + ------------------------------ + -------------- +
 | Ashua Hill     | 456 Erica Ct, Cupertino        | 111111         |
 + -------------- + ------------------------------ + -------------- +
 | Brian Reed     | 723 Kern Ave, Palo Alto        | 222222         |
 + -------------- + ------------------------------ + -------------- +

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 OVERWRITE students PARTITION (student_id = 222222) SELECT name, address FROM persons WHERE name = "Dora Williams";

SELECT * FROM students;

  + -------------- + ------------------------------ + -------------- +
  | name           | address                        | student_id     |
  + -------------- + ------------------------------ + -------------- +
  | Ashua Hill     | 456 Erica Ct, Cupertino        | 111111         |
  + -------------- + ------------------------------ + -------------- +
  | Dora Williams  | 134 Forest Ave, Melo Park      | 222222         |
  + -------------- + ------------------------------ + -------------- +

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 OVERWRITE students TABLE visiting_students;

SELECT * FROM students;

  + -------------- + ------------------------------ + -------------- +
  | name           | address                        | student_id     |
  + -------------- + ------------------------------ + -------------- +
  | 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 OVERWRITE students; FROM applicants SELECT name, address, id applicants WHERE qualified = true;

SELECT * FROM students;

 + -------------- + ------------------------------ + -------------- +
 | name           | address                        | student_id     |
 + -------------- + ------------------------------ + -------------- +
 | Helen Davis    | 469 Mission St, San Diego      | 999999         |
 + -------------- + ------------------------------ + -------------- +
 | Jason Wang     | 908 Bird St, Saratoga          | 121212         |
 + -------------- + ------------------------------ + -------------- +