SQL Server Error Messages - Msg 8107¶

Error Message¶

Server: Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table Table Name.
Cannot perform SET operation for table Table Name.

Causes:¶

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. This error occurs when executing a SET IDENTITY_INSERT ON command on a table when another table already has it set to ON. To illustrate:

CREATE TABLE [dbo].[IdentityInsert] (
[ID] INT NOT NULL IDENTITY(1,1),
[Name] VARCHAR(50))
GO

CREATE TABLE [dbo].[IdentityInsert2] (
[ID] INT NOT NULL IDENTITY(1,1),
[Name] VARCHAR(50))
GO

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert2] ON

Since the IDENTITY_INSERT has been set to ON for the [dbo].[IdentityInsert] table and it has not been set back to OFF, setting it to ON for another table, [dbo].[IdentityInsert2] generates this error.

Solution / Work Around:¶

To avoid this error, make sure that every time the IDENTITY_INSERT is set to ON, set it back to OFF after the INSERT statement.

SET IDENTITY_INSERT [dbo].[IdentityInsert] ON

INSERT INTO [dbo].[IdentityInsert] ( [ID], [Name] )
VALUES ( 2, 'Yankee Doodle')

SET IDENTITY_INSERT [dbo].[IdentityInsert] OFF

SET IDENTITY_INSERT [dbo].[IdentityInsert2] ON

INSERT INTO [dbo].[IdentityInsert2] ( [ID], [Name] )
VALUES ( 1, 'Mickey Mouse')

SET IDENTITY_INSERT [dbo].[IdentityInsert2] OFF