Search code examples
sqlinsertidentity-insert

Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF

I'm trying to insert records in a table that has got an identity column. For various reasons, this should not be a straight insert, but it should have the same ID as the foreign table. So I run:

EXECUTE ('SET IDENTITY_INSERT RemoteDB.dbo.FrameContent ON')

INSERT INTO [RemoteDB].[dbo].[FrameContent] ([ID],[FrameID],[PacketType]) 
SELECT [ID],[FrameID],[PacketType]
FROM FrameContent 
WHERE [ID] NOT IN (SELECT [ID] FROM [RemoteDB].[dbo].[FrameContent])

And I get the error:

Cannot insert explicit value for identity column in table 'FrameContent' when IDENTITY_INSERT is set to OFF.

Any ideas? It complains that IDENTITY_INSERT should not be OFF, but I am setting it to ON.

Solution

  • Try to set dentity_insert outside execute:

    SET IDENTITY_INSERT RemoteDB.dbo.FrameContent ON;
INSERT ...

    If you set it inside execute, it will only apply to other statements inside the execute.