2

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.

Cameron Castillo
  • 2,712
  • 10
  • 47
  • 77

1 Answers1

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 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 – Cameron Castillo Jun 20 '13 at 11:21
  • 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 – Don Rolling Jun 17 '14 at 16:04