|
|
|
SET IDENTITY_INSERT
Description
SET IDENTITY_INSERT toggles ability to insert user defined values into a field defined as an IDENTITY column.
Syntax
SET IDENTITY_INSERT <table> on | off;
Notes
Toggles IDENTITY insert values from and INSERT statement. If the value inserted is larger than the current identity value for the table, the new inserted value is used as the current identity value.
Only one table at a time within a session can be set to identity_insert on. An error is returned on the second attempt requiring the calling user to first disable support on the current table with SET IDENTITY_INSERT OFF.
Examples
CREATE TABLE salesdb (name CHAR(10), sales_id INTEGER IDENTITY (1,1);
SET IDENTITY_INSERT 'salesdb' ON;
INSERT VALUES ('joe', 199);
SET IDENTITY_INSERT 'salesdb' OFF:
INSERT VALUES ('sally');
Authorization
None.
