Inserting values into an identity column

 

You can insert a value into an identity column or allow the system to insert a value for you.

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.

 

Parent topic:

Inserting rows using the INSERT statement

 

Related reference

Creating and altering an identity column
Scalar functions