Set identity_insert
Explicit values can be inserted into the table's ID column.
Syntax
Set identity_insert [
Database. [owner.] {table} {on | off}
Parameters
Database
Is the name of the database where the specified table resides.
Owner
Is the name of the table owner.
Table
Is the name of the table containing the ID column.
Note
At any time, the identity_insert attribute of only one table in the session can be set to on. If a table has set this attribute to on and sends a set
The identity_insert on statement is Microsoft? SQL Server? Returns an error message indicating set identity_insert.
The table that has been set to on and reported that this attribute is set to on.
If the inserted value is greater than the current table id value, SQL Server automatically uses the new value as the current ID value.
Set identity_insert is set during execution or running, rather than during analysis.
Permission
SysAdmin is granted with execution permission by default.
Fixed server role, db_owner, db_ddladmin, fixed database role, and object owner.
Example
In the following example, create a table with an ID column and show how to use
Set identity_insert to fill in gaps in the ID values caused by the delete statement.
-- Create products table.
Create Table products (ID int identity primary key, product varchar (40 ))
Go
-- Inserting values into products table.
Insert into products
(Product) values ('screwdriver ')
Insert into products (product) Values
('Hammer ')
Insert into products (product) values ('saw ')
Insert
Products (product) values ('shovel ')
Go
-- Create a gap in the identity
Values.
Delete Products
Where product = 'saw'
Go
Select *
From Products
Go
-- Attempt to insert an explicit id value of 3;
-- Shoshould return a warning.
Insert into products (ID, product) values (3,
'Garden shovel ')
Go
-- Set identity_insert to on.
Set
Identity_insert products on
Go
-- Attempt to insert an explicit id value
Of 3
Insert into products (ID, product) values (3, 'garden shovel ').
Go
Select *
From Products
Go
-- Drop Products table.
Drop table
Products
Go
