|
|
|
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 an 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
User must own the table or have ALTER permission on the table..
