Inserting values into an identity column

For example, a table has columns called ORDERNO (identity column), SHIPPED_TO (varchar(36)), and ORDER_DATE (date). You can insert a row into this table by issuing the following statement:

INSERT INTO ORDERS (SHIPPED_TO, ORDER_DATE) 
    VALUES ('BME TOOL', '2002-02-04')

In this case, a value is generated by the system for the identity column automatically. You can also write this statement using the DEFAULT keyword:

INSERT INTO ORDERS (SHIPPED_TO, ORDER_DATE, ORDERNO) 
    VALUES ('BME TOOL', '2002-02-04', DEFAULT)

After the insert, you can use the IDENTITY_VAL_LOCAL function to determine the value that the system assigned to the column.

Sometimes a value for an identity column is specified by the user, such as in this INSERT statement using a SELECT:

INSERT INTO ORDERS OVERRIDING USER VALUE
   (SELECT * FROM TODAYS_ORDER)

In this case, OVERRIDING USER VALUE tells the system to ignore the value provided for the identity column from the SELECT and to generate a new value for the identity column. OVERRIDING USER VALUE must be used if the identity column was created with the GENERATED ALWAYS clause; it is optional for GENERATED BY DEFAULT. If OVERRIDING USER VALUE is not specified for a GENERATED BY DEFAULT identity column, the value provided for the column in the SELECT is inserted.

You can force the system to use the value from the select for a GENERATED ALWAYS identity column by specifying OVERRIDING SYSTEM VALUE. For example, issue the following statement:

INSERT INTO ORDERS OVERRIDING SYSTEM VALUE
   (SELECT * FROM TODAYS_ORDER)

This INSERT statement uses the value from SELECT; it does not generate a new value for the identity column. You cannot provide a value for an identity column created using GENERATED ALWAYS without using the OVERRIDING SYSTEM VALUE clause.