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

sql insert identity-insert
Cameron Castillo picture Cameron Castillo · Jun 20, 2013 · Viewed 12.7k times · Source

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.

Answer

Andomar picture Andomar · Jun 20, 2013

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.

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's the …

Read More
How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More