SET IDENTITY_INSERT (Transact-SQL)

Allows explicit values to be inserted into the identity column of a table.

Transact-SQL Syntax Conventions

Syntax

SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

Arguments

• database_name
  Is the name of the database in which the specified table resides.

• schema_name
  Is the name of the schema to which the table belongs.

• table
  Is the name of a table with an identity column.

Remarks

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server 2005 returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Permissions

User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.

Examples

The following example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

USE AdventureWorks;
GO
-- Create tool table.
CREATE TABLE dbo.Tool(
   ID INT IDENTITY NOT NULL PRIMARY KEY, 
   Name VARCHAR(40) NOT NULL
)
GO
-- Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO

-- Create a gap in the identity values.
DELETE dbo.Tool 
WHERE Name = 'Saw'
GO

SELECT * 
FROM dbo.Tool
GO

-- Try to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO

-- Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO

SELECT * 
FROM dbo.Tool
GO
-- Drop products table.
DROP TABLE dbo.Tool
GO

See Also

Reference

CREATE TABLE (Transact-SQL)
IDENTITY (Property) (Transact-SQL)
SCOPE_IDENTITY (Transact-SQL)
INSERT (Transact-SQL)
SET (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance