Explicitly inserting data into an IDENTITY column

At times, you may want to insert a specific value into an IDENTITY column. For example, you may want the first row inserted into the table to have an IDENTITY value of 101, rather than 1. Or you may need to reinsert a row that was deleted by mistake.

The table owner can explicitly insert a value into an IDENTITY column. The database owner and system administrator can explicitly insert a value into an IDENTITY column if they have been granted explicit permission by the table owner, or if they are acting as the table owner.

Before inserting the data, set the identity_insert option on for the table. You can set identity_insert on for only one table at a time in a database within a session.

This example specifies a “seed” value of 101 for the IDENTITY column:

set identity_insert sales_daily on
insert sales_daily (syb_identity, stor_id) 
values (101, "1349")

The insert statement lists each column, including the IDENTITY column, for which a value is specified. When the identity_insert option is set to on, each insert statement for the table must specify an explicit column list. The values list must specify an IDENTITY column value, since IDENTITY columns do not allow null values.

After you set identity_insert off, you can insert IDENTITY column values automatically, without specifying the IDENTITY column. Subsequent insertions use IDENTITY values based on the value explicitly specified after you set identity_insert on. For example, if you specify 101 for the IDENTITY column, subsequent insertions are 102, 103, and so on.