<commit_before>---
layout: global
title: INSERT INTO
displayTitle: INSERT INTO
license: |
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at
 
     http://www.apache.org/licenses/LICENSE-2.0
 
  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
---

### 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

```sql
INSERT INTO [ TABLE ] table_identifier [ partition_spec ] [ ( column_list ) ]
    { 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:
    * a `SELECT` statement
    * a `TABLE` statement
    * a `FROM` statement

### Examples

#### Single Row Insert Using a VALUES Clause

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

INSERT INTO students VALUES
    ('<NAME>', '123 Park Ave, San Jose', 111111);

SELECT * FROM students;<commit_msg>[MINOR][DOCS] Update sql-ref-syntax-dml-insert-into.md<commit_after>---
layout: global
title: INSERT INTO
displayTitle: INSERT INTO
license: |
  Licensed to the Apache Software Foundation (ASF) under one or more
  contributor license agreements.  See the NOTICE file distributed with
  this work for additional information regarding copyright ownership.
  The ASF licenses this file to You under the Apache License, Version 2.0
  (the "License"); you may not use this file except in compliance with
  the License.  You may obtain a copy of the License at
 
     http://www.apache.org/licenses/LICENSE-2.0
 
  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.
---

### 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

```sql
INSERT INTO [ TABLE ] table_identifier [ partition_spec ] [ ( column_list ) ]
    { 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:
    * a `SELECT` statement
    * a `TABLE` statement
    * a `FROM` statement

### Examples

#### Single Row Insert Using a VALUES Clause

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

INSERT INTO students VALUES
    ('<NAME>', '123 Park Ave, San Jose', 111111);

SELECT * FROM students;
{'fraction_non_alphanumeric': 0.08184352960472363, 'fraction_numerical': 0.008200754469411186, 'mean_word_length': 3.5849624060150376, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5921825', 'n_tokens_mistral': 1641, 'n_tokens_neox': 1531, 'n_words': 810}