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

Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. 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.

Remarks

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 = 'DBA.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.