Enables users to insert values into or to update an IDENTITY or AUTOINCREMENT column.
Can be set only temporary (for a connection), for a user, or for the PUBLIC group. Takes effect immediately.
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.
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.
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.