0001 ---
0002 layout: global
0003 title: INSERT INTO
0004 displayTitle: INSERT INTO
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `INSERT INTO` statement inserts new rows into a table. The inserted rows can be specified by value expressions or result from a query.
0025
0026 ### Syntax
0027
0028 ```sql
0029 INSERT INTO [ TABLE ] table_identifier [ partition_spec ]
0030 { VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ] | query }
0031 ```
0032
0033 ### Parameters
0034
0035 * **table_identifier**
0036
0037 Specifies a table name, which may be optionally qualified with a database name.
0038
0039 **Syntax:** `[ database_name. ] table_name`
0040
0041 * **partition_spec**
0042
0043 An optional parameter that specifies a comma separated list of key and value pairs
0044 for partitions.
0045
0046 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0047
0048 * **VALUES ( { value `|` NULL } [ , ... ] ) [ , ( ... ) ]**
0049
0050 Specifies the values to be inserted. Either an explicitly specified value or a NULL can be inserted.
0051 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.
0052
0053 * **query**
0054
0055 A query that produces the rows to be inserted. It can be in one of following formats:
0056 * a `SELECT` statement
0057 * a `TABLE` statement
0058 * a `FROM` statement
0059
0060 ### Examples
0061
0062 #### Single Row Insert Using a VALUES Clause
0063
0064 ```sql
0065 CREATE TABLE students (name VARCHAR(64), address VARCHAR(64), student_id INT)
0066 USING PARQUET PARTITIONED BY (student_id);
0067
0068 INSERT INTO students VALUES
0069 ('Amy Smith', '123 Park Ave, San Jose', 111111);
0070
0071 SELECT * FROM students;
0072 +---------+---------------------+----------+
0073 | name| address|student_id|
0074 +---------+---------------------+----------+
0075 |Amy Smith|123 Park Ave,San Jose| 111111|
0076 +---------+---------------------+----------+
0077 ```
0078
0079 #### Multi-Row Insert Using a VALUES Clause
0080
0081 ```sql
0082 INSERT INTO students VALUES
0083 ('Bob Brown', '456 Taylor St, Cupertino', 222222),
0084 ('Cathy Johnson', '789 Race Ave, Palo Alto', 333333);
0085
0086 SELECT * FROM students;
0087 +-------------+------------------------+----------+
0088 | name| address|student_id|
0089 +-------------+------------------------+----------+
0090 | Amy Smith| 123 Park Ave, San Jose| 111111|
0091 +-------------+------------------------+----------+
0092 | Bob Brown|456 Taylor St, Cupertino| 222222|
0093 +-------------+------------------------+----------+
0094 |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
0095 +--------------+-----------------------+----------+
0096 ```
0097
0098 #### Insert Using a SELECT Statement
0099
0100 ```sql
0101 -- Assuming the persons table has already been created and populated.
0102 SELECT * FROM persons;
0103 +-------------+-------------------------+---------+
0104 | name| address| ssn|
0105 +-------------+-------------------------+---------+
0106 |Dora Williams|134 Forest Ave, Melo Park|123456789|
0107 +-------------+-------------------------+---------+
0108 | Eddie Davis| 245 Market St, Milpitas|345678901|
0109 +-------------+-------------------------+---------+
0110
0111 INSERT INTO students PARTITION (student_id = 444444)
0112 SELECT name, address FROM persons WHERE name = "Dora Williams";
0113
0114 SELECT * FROM students;
0115 +-------------+-------------------------+----------+
0116 | name| address|student_id|
0117 +-------------+-------------------------+----------+
0118 | Amy Smith| 123 Park Ave, San Jose| 111111|
0119 +-------------+-------------------------+----------+
0120 | Bob Brown| 456 Taylor St, Cupertino| 222222|
0121 +-------------+-------------------------+----------+
0122 |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
0123 +-------------+-------------------------+----------+
0124 |Dora Williams|134 Forest Ave, Melo Park| 444444|
0125 +-------------+-------------------------+----------+
0126 ```
0127
0128 #### Insert Using a TABLE Statement
0129
0130 ```sql
0131 -- Assuming the visiting_students table has already been created and populated.
0132 SELECT * FROM visiting_students;
0133 +-------------+---------------------+----------+
0134 | name| address|student_id|
0135 +-------------+---------------------+----------+
0136 |Fleur Laurent|345 Copper St, London| 777777|
0137 +-------------+---------------------+----------+
0138 |Gordon Martin| 779 Lake Ave, Oxford| 888888|
0139 +-------------+---------------------+----------+
0140
0141 INSERT INTO students TABLE visiting_students;
0142
0143 SELECT * FROM students;
0144 +-------------+-------------------------+----------+
0145 | name| address|student_id|
0146 +-------------+-------------------------+----------+
0147 | Amy Smith| 123 Park Ave,San Jose| 111111|
0148 +-------------+-------------------------+----------+
0149 | Bob Brown| 456 Taylor St, Cupertino| 222222|
0150 +-------------+-------------------------+----------+
0151 |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
0152 +-------------+-------------------------+----------+
0153 |Dora Williams|134 Forest Ave, Melo Park| 444444|
0154 +-------------+-------------------------+----------+
0155 |Fleur Laurent| 345 Copper St, London| 777777|
0156 +-------------+-------------------------+----------+
0157 |Gordon Martin| 779 Lake Ave, Oxford| 888888|
0158 +-------------+-------------------------+----------+
0159 ```
0160
0161 #### Insert Using a FROM Statement
0162
0163 ```sql
0164 -- Assuming the applicants table has already been created and populated.
0165 SELECT * FROM applicants;
0166 +-----------+--------------------------+----------+---------+
0167 | name| address|student_id|qualified|
0168 +-----------+--------------------------+----------+---------+
0169 |Helen Davis| 469 Mission St, San Diego| 999999| true|
0170 +-----------+--------------------------+----------+---------+
0171 | Ivy King|367 Leigh Ave, Santa Clara| 101010| false|
0172 +-----------+--------------------------+----------+---------+
0173 | Jason Wang| 908 Bird St, Saratoga| 121212| true|
0174 +-----------+--------------------------+----------+---------+
0175
0176 INSERT INTO students
0177 FROM applicants SELECT name, address, id applicants WHERE qualified = true;
0178
0179 SELECT * FROM students;
0180 +-------------+-------------------------+----------+
0181 | name| address|student_id|
0182 +-------------+-------------------------+----------+
0183 | Amy Smith| 123 Park Ave, San Jose| 111111|
0184 +-------------+-------------------------+----------+
0185 | Bob Brown| 456 Taylor St, Cupertino| 222222|
0186 +-------------+-------------------------+----------+
0187 |Cathy Johnson| 789 Race Ave, Palo Alto| 333333|
0188 +-------------+-------------------------+----------+
0189 |Dora Williams|134 Forest Ave, Melo Park| 444444|
0190 +-------------+-------------------------+----------+
0191 |Fleur Laurent| 345 Copper St, London| 777777|
0192 +-------------+-------------------------+----------+
0193 |Gordon Martin| 779 Lake Ave, Oxford| 888888|
0194 +-------------+-------------------------+----------+
0195 | Helen Davis|469 Mission St, San Diego| 999999|
0196 +-------------+-------------------------+----------+
0197 | Jason Wang| 908 Bird St, Saratoga| 121212|
0198 +-------------+-------------------------+----------+
0199 ```
0200
0201 ### Related Statements
0202
0203 * [INSERT OVERWRITE statement](sql-ref-syntax-dml-insert-overwrite-table.html)
0204 * [INSERT OVERWRITE DIRECTORY statement](sql-ref-syntax-dml-insert-overwrite-directory.html)
0205 * [INSERT OVERWRITE DIRECTORY with Hive format statement](sql-ref-syntax-dml-insert-overwrite-directory-hive.html)
