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

Question

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.

Sorry - copy and paste issue. I'm actually compiling a string with variables. Should be SET for this example. — Cameron Castillo
– Cameron Castillo, Commented Jun 20, 2013 at 11:19

Andomar · Accepted Answer · 2013-06-20 11:06:01Z

9

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.

answered Jun 20, 2013 at 11:06

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cameron Castillo Over a year ago

Yip, that will work. Now I have to figure out how to pass the table name as a variable (that was the reason for using the Execute). Thanks

Don Rolling Over a year ago

Here you go. code declare @tables Table(id smallint Primary Key IDENTITY(1,1), name nvarchar(100)) insert @tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' declare @i int declare @numrows int declare @tableName nvarchar(100) set @i = 1 set @numrows = (SELECT COUNT(*) FROM @tables) IF @numrows > 0 WHILE (@i <= (SELECT MAX(id) FROM @tables)) BEGIN SET @tableName = (SELECT name FROM @tables WHERE id = @i) exec [dbo].[whateverproc] @tableName SET @i = @i + 1 END

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related