INSERT

Updates a table by inserting one or more rows into the table. The values inserted into each column in the table can be explicitly-specified or the results of a query.

See also:

INSERT (multi-table)

Syntax

INSERT [ OVERWRITE ] INTO <target_table> [ ( <target_col_name> [ , ... ] ) ]
       {
         VALUES ( { <value> | DEFAULT | NULL } [ , ... ] ) [ , ( ... ) ]  |
         <query>
       }
Copy

Required parameters

target_table

Specifies the target table into which to insert rows.

VALUES ( value | DEFAULT | NULL [ , ... ] )  [ , ( ... ) ]

Specifies one or more values to insert into the corresponding columns in the target table.

In a VALUES clause, you can specify the following:

  • value: Inserts the explicitly-specified value. The value may be a literal or an expression.

  • DEFAULT: Inserts the default value for the corresponding column in the target table.

  • NULL: Inserts a NULL value.

Each value in the clause must be separated by a comma.

You can insert multiple rows by specifying additional sets of values in the clause. For more details, see the Usage Notes and the Examples (in this topic).

query

Specify a query statement that returns values to be inserted into the corresponding columns. This allows you to insert rows into a target table from one or more source tables.

Optional parameters

OVERWRITE

Specifies that the target table should be truncated before inserting the values into the table. Note that specifying this option does not affect the access control privileges on the table.

INSERT statements with OVERWRITE can be processed within the scope of the current transaction, which avoids DDL statements that commit a transaction, such as:

DROP TABLE t;
CREATE TABLE t AS SELECT * FROM ... ;
Copy

Default: No value (the target table is not truncated before performing the inserts).

( target_col_name [ , ... ] )

Specifies one or more columns in the target table into which the corresponding values are inserted. The number of target columns specified must match the number of specified values or columns (if the values are the results of a query) in the VALUES clause.

Default: No value (all the columns in the target table are updated).

Usage notes

  • Using a single INSERT command, you can insert multiple rows into a table by specifying additional sets of values separated by commas in the VALUES clause.

    For example, the following clause would insert 3 rows in a 3-column table, with values 1, 2, and 3 in the first two rows and values 2, 3, and 4 in the third row:

    VALUES ( 1, 2, 3 ) ,
       ( 1, 2, 3 ) ,
       ( 2, 3, 4 )
    Copy

  • To use the OVERWRITE option on INSERT, you must use a role that has DELETE privilege on the table because OVERWRITE will delete the existing records in the table.

  • Some expressions cannot be specified in the VALUES clause. As an alternative, specify the expression in a query clause. For example, you can replace:

    INSERT INTO table1 (ID, varchar1, variant1)
    VALUES (4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }'));
    Copy

    with:

    INSERT INTO table1 (ID, varchar1, variant1)
    SELECT 4, 'Fourier', PARSE_JSON('{ "key1": "value1", "key2": "value2" }');
    Copy

  • The VALUES clause is limited to 16,384 rows. This limit applies to a single INSERT INTO … VALUES statement and a single INSERT INTO … SELECT … FROM VALUES statement. Consider using the COPY INTO <table> command to perform a bulk data load. For more information about using the VALUES clause in a SELECT statement, see VALUES.

  • For information about inserting data into hybrid tables, see Loading data.

Examples

Single row insert using a query

Convert three string values to dates or timestamps and insert them into a single row in the mytable table:

DESC TABLE mytable;

+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type             | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| COL1 | DATE             | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| COL2 | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| COL3 | TIMESTAMP_NTZ(9) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

INSERT INTO mytable
  SELECT TO_DATE('2013-05-08T23:39:20.123'), TO_TIMESTAMP('2013-05-08T23:39:20.123'), TO_TIMESTAMP('2013-05-08T23:39:20.123');

SELECT * FROM mytable;

+------------+-------------------------+-------------------------+
| COL1       | COL2                    | COL3                    |
|------------+-------------------------+-------------------------|
| 2013-05-08 | 2013-05-08 23:39:20.123 | 2013-05-08 23:39:20.123 |
+------------+-------------------------+-------------------------+
Copy

Similar to previous example, but specify to update only the first and third columns in the table:

INSERT INTO mytable (col1, col3)
  SELECT TO_DATE('2013-05-08T23:39:20.123'), TO_TIMESTAMP('2013-05-08T23:39:20.123');

SELECT * FROM mytable;

+------------+-------------------------+-------------------------+
| COL1       | COL2                    | COL3                    |
|------------+-------------------------+-------------------------|
| 2013-05-08 | 2013-05-08 23:39:20.123 | 2013-05-08 23:39:20.123 |
| 2013-05-08 | NULL                    | 2013-05-08 23:39:20.123 |
+------------+-------------------------+-------------------------+
Copy

Multi-row insert using explicitly-specified values

Insert two rows of data into the employees table by providing both sets of values in a comma-separated list in the VALUES clause:

SELECT * FROM employees;

+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-859-3954 | San Francisco | 94115       |
+------------+-----------+----------------+---------------+-------------+

INSERT INTO employees
  VALUES
  ('Lysandra','Reeves','1-212-759-3751','New York',10018),
  ('Michael','Arnett','1-650-230-8467','San Francisco',94116);

SELECT * FROM employees;

+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-859-3954 | San Francisco | 94115       |
| Lysandra   | Reeves    | 1-212-759-3751 | New York      | 10018       |
| Michael    | Arnett    | 1-650-230-8467 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+
Copy

In multi-row inserts, make sure that the data types of the inserted values are consistent across the rows because the data type of the first row is used as a guide. The following will fail because the data type of the value in the second row is different from the data type of the value in the first row, even though both values can be coerced to VARCHAR, which is the data type of the column in the table:

CREATE TABLE t1 (v VARCHAR);

-- works as expected.
INSERT INTO t1 (v) VALUES
   ('three'),
   ('four');

-- Fails with error "Numeric value 'd' is not recognized"
-- even though the data type of 'd' is the same as the
-- data type of the column v.
INSERT INTO t1 (v) VALUES
   (3),
   ('d');
Copy

Multi-row insert using query

Insert multiple rows of data from the contractors table into the employees table:

  • Select only those rows where the worknum column contains area code 650.

  • Insert a NULL value in the city column.

SELECT * FROM employees;

+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-859-3954 | San Francisco | 94115       |
| Lysandra   | Reeves    | 1-212-759-3751 | New York      | 10018       |
| Michael    | Arnett    | 1-650-230-8467 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+

SELECT * FROM contractors;

+------------------+-----------------+----------------+---------------+----------+
| CONTRACTOR_FIRST | CONTRACTOR_LAST | WORKNUM        | CITY          | ZIP_CODE |
|------------------+-----------------+----------------+---------------+----------|
| Bradley          | Greenbloom      | 1-650-445-0676 | San Francisco | 94110    |
| Cole             | Simpson         | 1-212-285-8904 | New York      | 10001    |
| Laurel           | Slater          | 1-650-633-4495 | San Francisco | 94115    |
+------------------+-----------------+----------------+---------------+----------+

INSERT INTO employees(first_name, last_name, workphone, city,postal_code)
  SELECT
    contractor_first,contractor_last,worknum,NULL,zip_code
  FROM contractors
  WHERE CONTAINS(worknum,'650');

SELECT * FROM employees;

+------------+------------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME  | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+------------+----------------+---------------+-------------|
| May        | Franklin   | 1-650-249-5198 | San Francisco | 94115       |
| Gillian    | Patterson  | 1-650-859-3954 | San Francisco | 94115       |
| Lysandra   | Reeves     | 1-212-759-3751 | New York      | 10018       |
| Michael    | Arnett     | 1-650-230-8467 | San Francisco | 94116       |
| Bradley    | Greenbloom | 1-650-445-0676 | NULL          | 94110       |
| Laurel     | Slater     | 1-650-633-4495 | NULL          | 94115       |
+------------+------------+----------------+---------------+-------------+
Copy

Insert multiple rows of data from the contractors table into the employees table using a common table expression:

INSERT INTO employees (first_name,last_name,workphone,city,postal_code)
  WITH cte AS
    (SELECT contractor_first AS first_name,contractor_last AS last_name,worknum AS workphone,city,zip_code AS postal_code
     FROM contractors)
  SELECT first_name,last_name,workphone,city,postal_code
  FROM cte;
Copy

Insert columns from two tables (emp_addr, emp_ph) into a third table (emp) using an INNER JOIN on the id column in the source tables:

INSERT INTO emp (id,first_name,last_name,city,postal_code,ph)
  SELECT a.id,a.first_name,a.last_name,a.city,a.postal_code,b.ph
  FROM emp_addr a
  INNER JOIN emp_ph b ON a.id = b.id;
Copy

Multi-row insert for JSON data

Insert two JSON objects into a VARIANT column in a table:

INSERT INTO prospects
  SELECT PARSE_JSON(column1)
  FROM VALUES
  ('{
    "_id": "57a37f7d9e2b478c2d8a608b",
    "name": {
      "first": "Lydia",
      "last": "Williamson"
    },
    "company": "Miralinz",
    "email": "lydia.williamson@miralinz.info",
    "phone": "+1 (914) 486-2525",
    "address": "268 Havens Place, Dunbar, Rhode Island, 7725"
  }')
  , ('{
    "_id": "57a37f7d622a2b1f90698c01",
    "name": {
      "first": "Denise",
      "last": "Holloway"
    },
    "company": "DIGIGEN",
    "email": "denise.holloway@digigen.net",
    "phone": "+1 (979) 587-3021",
    "address": "441 Dover Street, Ada, New Mexico, 5922"
  }');
Copy

Insert using overwrite

This example uses INSERT with OVERWRITE to rebuild the sf_employees table from employees after new records were added to the employees table.

Here is the initial data for both tables:

SELECT * FROM employees;
+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-111-1111 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-222-2222 | San Francisco | 94115       |
| Lysandra   | Reeves    | 1-212-222-2222 | New York      | 10018       |
| Michael    | Arnett    | 1-650-333-3333 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+
SELECT * FROM sf_employees;
+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| Martin     | Short     | 1-650-999-9999 | San Francisco | 94115       |
+------------+-----------+----------------+---------------+-------------+
Copy

This statement inserts into the sf_employees table using OVERWRITE clause:

INSERT OVERWRITE INTO sf_employees
  SELECT * FROM employees
  WHERE city = 'San Francisco';
Copy

Because the INSERT used the OVERWRITE option, the old row(s) of sf_employees are gone:

SELECT * FROM sf_employees;
+------------+-----------+----------------+---------------+-------------+
| FIRST_NAME | LAST_NAME | WORKPHONE      | CITY          | POSTAL_CODE |
|------------+-----------+----------------+---------------+-------------|
| May        | Franklin  | 1-650-111-1111 | San Francisco | 94115       |
| Gillian    | Patterson | 1-650-222-2222 | San Francisco | 94115       |
| Michael    | Arnett    | 1-650-333-3333 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+
Copy
Language: English