IDENTITY_INSERT Option

Enables users to insert values into or to update an IDENTITY or AUTOINCREMENT column.

Allowed Values

= 'tablename'

Default

Option not set.

Scope

Can be set only temporary (for a connection), for a user, or for the PUBLIC group. Takes effect immediately.

Note: If you set a user level option for the current option, the corresponding temporary option is also set. See Scope and Duration of Database Options.

Description

When IDENTITY_INSERT is set, insert/update is enabled. A table name must be specified to identify the column to insert or update. If you are not the table owner, qualify the table name with the owner name.

To drop a table with an IDENTITY column, IDENTITY_INSERT must not be set to that table.

Examples

If you use the table Employees to run explicit inserts:

SET TEMPORARY OPTION IDENTITY_INSERT = 'Employees'

To turn the option off, specify the equals sign and an empty string:

SET TEMPORARY OPTION IDENTITY_INSERT = ''

Illustrates the effect of user level options on temporary options (see Note), if you are connected to the database as DBA and enter:

SET OPTION IDENTITY_INSERT = 'Customers'

The value for the option is set to Customers for the user DBA and temporary for the current connection. Other users who subsequently connect to the database as DBA find their option value for IDENTITY_INSERT is Customers also.