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.

Note: 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). 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. 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. 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. If one or more columns were not defined with a DEFAULT value, the INSERT statement will fail (see Example 5).

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).

Skip All Column Update Write Related Processing on Duplicate Inserts

In order to avoid performing column update processing during duplicate row insert attempts, see the following 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

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 SQL QuickStart.