Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : SQL Server Management Studio Shortcuts
Error Messages
Home > SQL Server Error Messages > Msg 8107 - IDENTITY_INSERT is already ON for table Table Name
SQL Server Error Messages - Msg 8107 - IDENTITY_INSERT is already ON for table Table Name

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
Related Articles :