|
|
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
|