Categories:

DML Commands - General

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)

In this Topic:

Syntax

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

Required Parameters

target_table

Specifies the target table into which to insert rows. The same table may be referenced more than once (in separate WHEN clauses).

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

Specifies one or more values to insert into the corresponding columns in the target table. The values can either be the results of a query or explicitly-specified (using a VALUES clause):

  • For a query, specify a SELECT 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.

  • In a VALUES clause, you can specify the following:

    • value: Inserts the explicitly-specified value.

    • 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 Usage Notes (in this topic).

Optional Parameters

OVERWRITE

Specifies to truncate the target table before inserting into the table, while retaining 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 ... ;

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)

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

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

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

In multi-row inserts, make sure that the data types of the inserted values are consistent across the rows because the server looks at the data type of the first row 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');

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

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;

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;

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"
  }');

Insert Using Overwrite

Insert using overwrite to rebuild sf_employees table from employees after new records were added to employees:

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

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

SELECT * FROM sf_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       |
| Michael    | Arnett    | 1-650-230-8467 | San Francisco | 94116       |
+------------+-----------+----------------+---------------+-------------+

INSERT INTO employees
  VALUES ('Laurel', 'Slater', '1-650-633-4495', 'San Francisco', '94112');

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

SELECT * FROM sf_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       |
| Michael    | Arnett    | 1-650-230-8467 | San Francisco | 94116       |
| Laurel     | Slater    | 1-650-633-4495 | San Francisco | 94112       |
+------------+-----------+----------------+---------------+-------------+