INSERT

INSERT — creates one or more new rows in a table

Syntax

INSERT INTO [schema.]table_name [ ( column_name [, ...] ) ]
            {  VALUES ( [ expression [, ...] ] )[, (expression [, ...]) ... ]
             | SELECT query
             | DEFAULT VALUES
            }
            [ ON DUPLICATE KEY [SKIP|UPDATE
                  { column_name = { VALUES(column_name) | expression } } ,... ]]

Description

Use the INSERT statement to add one or more rows to a table. The list of columns directly following the “INSERT INTO …​” is referred to as the column list. The column list may or may not include every column defined in the table. Columns defined in a table may appear in any order in the column list. The column list itself is optional. If a column is omitted from the column list, the column will be assigned the DEFAULT value that was defined for it when it was added to the table. If a DEFAULT value was not defined, the column will be assigned a NULL value. An INSERT statement will fail if a NULL value is assigned to a column that is defined as NOT NULL. If the value assigned to a column is not the correct data type, automatic type coercion is attempted.

Columns defined as GENERATED ALWAYS AS IDENTITY cannot be included in the column list. Therefore, a column list, if used, must list one or more of the remaining columns in the table being assigned a value.

VALUES

The VALUES option provides values for columns in rows being inserted. The list of columns after the VALUES keyword is referred to as the value list. The value list must list columns in the same order as the columns appear in the column list to which they are assigning a value, see Example 1. If the column list is omitted, the value list must include all columns defined in the table and must list those columns in the order that they are defined in the table, see Example 2.

If the value list does not include values for all columns defined, a “too few values specified in the value list” error is returned.

Multiple rows can be inserted into a table by providing multiple sets of value lists, each separated by a comma and each enclosed by their own parentheses. If a table is created with columns that are all defined with a default value or an auto-generated identifier, the VALUES option can be used without having to provide a list of values for each column. Any rows being inserted would be inserted with default values, see Example 4. Any number of columns in the table can be specified in the column list of the insert statement.

SELECT

The SELECT option defines a SELECT statement that returns rows to be inserted. The list of columns in the SELECT statement is referred to as the select column list. The select column list must list the columns in the same order as the columns appear in the column list of the INSERT statement. If the column list is omitted, the select column list must include all columns defined in the table and must list those columns in the order that they are defined in the table. If the value list does not include values for all columns defined, a “too few values specified in the value list” error is returned.

One row is inserted into the table for each row that is returned from the SELECT statement. An asterisk, “*”, can be used in the place of a select column list providing the columns are being selected in the correct order, see Example 3. A combination of a SELECT statement and providing a list of values can also be provided for inserted values, see Example 4.

DEFAULT VALUES

The DEFAULT VALUES option inserts one row into the table. It inserts NULL values in the fields that don’t have a DEFAULT value specified when the table was created, and the INSERT statement will execute successfully. Each column in the column list is assigned the DEFAULT value specified when the column was added to the table. If the column list is omitted, the INSERT statement attempts to assign every column in the table with the default value that was defined for the column when it was added.

ON DUPLICATE KEY UPDATE

This option handles the error condition where a row is attempted to be inserted into a table that is defined with a UNIQUE constraint on one or more columns and the row being inserted fails that UNIQUE constraint. This option provides the ability to re-assign the value of one or more columns of an already existing row, see Example 6. If the VALUES keyword is used, the column_name following the VALUES keyword must be a column_name specified in the column list of the INSERT statement, see Example 7. Optionally, this can behave similar to ignoring duplicate rows on insert, where only new rows are inserted and duplicate rows are ignored. This can be done by updating existing rows with their current value. This provides the ability to override a duplicate unique key error when performing bulk inserts, see Example 8).

ON DUPLICATE KEY SKIP

To avoid performing column update processing during duplicate row insert attempts, use ON DUPLICATE KEY SKIP. For example:

INSERT INTO tsttable
              VALUES ('a','a','a'),('b','b','b'),('c','c','c'),('a','a','a')
              ON DUPLICATE KEY SKIP;

This command will not process column updates on the duplicate row insert attempt ('a','a','a').

Parameters

table_name

Name (optionally schema-qualified) of an existing table or updatable view, see Using Updatable Views. The user must have been granted INSERT privilege on the table.

column_name

Name of a column in the table on which the the INSERT is performed.

expression

A valid expression or value to assign to column_name.

query

A valid SELECT statement. Refer to the SELECT statement for a further description of valid arguments, see SELECT. The user must have been granted SELECT privileges on any table(s) specified in the SELECT statement.

Examples

Several of the examples below make use of tables in the HOCKEY schema, which is a schema defined in the test database. The test database is created when the QuickStart process is run. See Running the QuickStart Demo.

Example 1: Insert a row into the HOCKEY table, using VALUES and providing a column list.
INSERT INTO hockey.hockey (number, name, position, team)
VALUES (99, 'TOM JONES', 'Goalie', 'Bruins');
Example 2: Insert a row into the SCORING table, using VALUES without providing a column list.
INSERT INTO hockey.scoring
   VALUES ('aaltoan01', 2001, 1, 'ANA', 'C', 3, 0, 0, 0);
INSERT INTO hockey.scoring
   VALUES ('aaltoan01', 2002, 1, 'ANA', 'C', 3, 0, 0, 0), ('aaltoan01', 2003, 1, 'ANA', 'C', 3, 0, 0, 0);
Example 3: Insert a row into a table, using a select statement.
CREATE TABLE hockey.hockey_dup (id        INTEGER  PRIMARY KEY,
                                number    INTEGER,
                                name      STRING,
                                position  STRING,
                                team      STRING);
INSERT INTO hockey.hockey_dup
  SELECT * FROM hockey.hockey WHERE number > 40;

SELECT * FROM hockey.hockey_dup;
ID  NUMBER        NAME        POSITION   TEAM
 --- ------- ----------------- --------- ------
  2    48    CHRIS BOURQUE      Forward  Bruins
  6    46    DAVID KREJCI       Forward  Bruins
  8    64    LANE MACDERMID     Forward  Bruins
  9    63    BRAD MARCHAND      Forward  Bruins
 11    49    RICH PEVERLEY      Forward  Bruins
 12    91    MARC SAVARD        Forward  Bruins
 15    55    JOHNNY BOYCHUK     Defense  Bruins
 19    45    AARON JOHNSON      Defense  Bruins
 20    54    ADAM MCQUAID       Defense  Bruins
 21    44    DENNIS SEIDENBERG  Defense  Bruins
Example 4: Insert a row into a table using a combination of a SELECT statement and a list of values.

Insert a row into a table, using a combination of a select statement and a list of values. When using a combination, if the select statement is using an “”, the table must be assigned an alias and the “” must refer to the table alias. Otherwise an error is displayed.

CREATE TABLE hockey.hockey_bkup (id            INTEGER  PRIMARY KEY,
                                number         INTEGER,
                                name           STRING,
                                position       STRING,
                                team           STRING,
                                bkup_timestamp TIMESTAMP,
                                active         BOOLEAN);
INSERT INTO hockey.hockey_bkup
  SELECT *,current_timestamp,'true' FROM hockey.hockey_dup
      WHERE name < 'B';
/* ERROR: ^ expected FROM clause got , */

INSERT INTO hockey.hockey_bkup
  SELECT hd.*,current_timestamp,'true' FROM hockey.hockey_dup hd
      WHERE hd.name < 'B';

SELECT * FROM hockey.hockey_bkup;
 ID  NUMBER        NAME        POSITION   TEAM        BKUP_TIMESTAMP       ACTIVE
 --- ------- ----------------- --------- ------ -------------------------- -------
 19    45    AARON JOHNSON      Defense  Bruins 2015-04-23 10:19:12.316177  TRUE
 20    54    ADAM MCQUAID       Defense  Bruins 2015-04-23 10:19:12.316177  TRUE

INSERT INTO hockey.hockey_bkup
  SELECT id ,number ,name ,position ,team ,current_timestamp,'true'
     FROM hockey.hockey_dup
     WHERE name like 'B%';

SELECT * FROM hockey.hockey_bkup ORDER BY name;
 ID  NUMBER      NAME      POSITION   TEAM        BKUP_TIMESTAMP       ACTIVE
 --- ------- ------------- --------- ------ -------------------------- -------
 19    45    AARON JOHNSON  Defense  Bruins 2015-04-23 10:23:24.788419  TRUE
 20    54    ADAM MCQUAID   Defense  Bruins 2015-04-23 10:23:24.788419  TRUE
  9    63    BRAD MARCHAND  Forward  Bruins 2015-04-23 10:26:23.338989  TRUE
Example 5: Insert a row using all default values.
CREATE TABLE hockey.hockey_fans (id       INTEGER  GENERATED ALWAYS AS IDENTITY,
                                 name     STRING   DEFAULT 'Name Not Provided',
                                 address  STRING   DEFAULT 'Address Unknown');
INSERT INTO hockey.hockey_fans DEFAULT VALUES;

SELECT * FROM hockey.hockey_fans;
 ID        NAME            ADDRESS
 --- ----------------- ---------------
  1  Name Not Provided Address Unknown

/* This can also be written as: */
INSERT INTO hockey.hockey_fans VALUES ();
SELECT * FROM hockey.hockey_fans;
 ID        NAME            ADDRESS
 --- ----------------- ---------------
  1  Name Not Provided Address Unknown
  2  Name Not Provided Address Unknown

Example 6: Insert a row into a table with unique constraints and handling duplicate value errors. Update uses expression.::

Using the table created in Example 3 above, insert new values and update any existing values.

INSERT INTO hockey.hockey_dup
    SELECT * FROM hockey.hockey WHERE position = 'Defense'
    ON DUPLICATE KEY UPDATE position = position||'Dup';

SELECT * FROM hockey.hockey_dup;
 ID  NUMBER        NAME         POSITION   TEAM
 --- ------- ----------------- ---------- ------
  2    48    CHRIS BOURQUE     Forward    Bruins
  6    46    DAVID KREJCI      Forward    Bruins
  8    64    LANE MACDERMID    Forward    Bruins
  9    63    BRAD MARCHAND     Forward    Bruins
 11    49    RICH PEVERLEY     Forward    Bruins
 12    91    MARC SAVARD       Forward    Bruins
 15    55    JOHNNY BOYCHUK    DefenseDup Bruins  /* updated the next 4 rows */
 19    45    AARON JOHNSON     DefenseDup Bruins
 20    54    ADAM MCQUAID      DefenseDup Bruins
 21    44    DENNIS SEIDENBERG DefenseDup Bruins
 16    33    ZDENO CHARA       Defense    Bruins /* inserted these next 3 rows */
 17    21    ANDREW FERENCE    Defense    Bruins
 18    27    DOUGIE HAMILTON   Defense    Bruins
Example 7: Insert a row into a table with unique constraints and handling duplicate value errors. Update uses VALUES.

Using the table from Example 5 above, on insert if exists update columns using values provided.

INSERT INTO hockey.hockey_dup (id, number, name, position, team)
    VALUES (18, 27, 'TIM THOMAS', 'Goalie', 'Bruins')
    ON DUPLICATE KEY UPDATE number   = 101,
                            name     = VALUES(name),
                            position = VALUES(position),
                            team     = VALUES(team);

SELECT * FROM hockey.hockey_dup WHERE id = 18;
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 18    101   TIM THOMAS  Goalie   Bruins
Example 8: Performing bulk inserts into a table while ignoring duplicate unique key

In this example, we have a table where all the columns are part of a unique key. We want to do bulk inserts and ignore any duplicates and not generate an error. We do this by updating the table with the values that are already existing in the table.

DROP TABLE IF EXISTS tsttable;
CREATE TABLE tsttable (
    column1 STRING,
    column2 STRING,
    column3 STRING,
    PRIMARY KEY (column1, column2, column3) );
INSERT INTO tsttable VALUES ('a','a','a'), ('b','b','b'), ('c','c','c');

SELECT * FROM tsttable;
 COLUMN1  COLUMN2  COLUMN3
 -------- -------- --------
    a        a        a
    b        b        b
    c        c        c

INSERT INTO tsttable VALUES ('a','a','a'), ('b','b','b'), ('c','c','c'),('d','d','d');
/* ERROR:  duplicate value in unique index TSTTABLE..PRIMARY_KEY, key = 'a, a, a' */

SELECT * FROM tsttable;
 COLUMN1  COLUMN2  COLUMN3
 -------- -------- --------
    a        a        a
    b        b        b
    c        c        c

INSERT INTO tsttable VALUES ('a','a','a'), ('b','b','b'), ('c','c','c'),('d','d','d')
  ON DUPLICATE KEY UPDATE column1=column1, column2=column2, column3=column3;

SELECT * FROM tsttable;
 COLUMN1  COLUMN2  COLUMN3
 -------- -------- --------
    a        a        a
    b        b        b
    c        c        c
    d        d        d