0

I'm getting an error when trying to execute the following statements complaining that IDENTITY_INSERT must be on in order for the insert to occur, but I am turning it on.

SET IDENTITY_INSERT Thompson_GDM.dbo.employees ON    
GO

INSERT INTO Thompson_GDM.dbo.employees    
SELECT [EmployeeId],    
       [AssignmentId],    
       [more columns...]    
FROM TMLAVSQLBIS.ThompsonMachinery.dbo.Employees;    
GO

SET IDENTITY_INSERT Thompson_GDM.dbo.employees OFF    
go

The error message is "An explicit value for the identity column in table 'Thompson_GDM.dbo.employees' can only be specified when a column list is used and IDENTITY_INSERT is ON."

As you can see, I am using a column list & it matches the table layout. I have checked it several times & it does match

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Tommy W Holden
  • 45
  • 7
  • The error message is telling you two things to check. Have you tried `INSERT INTO Thompson_GDM.dbo.employees (EmployeeId, AssignmentId, [more columns...]) SELECT ...` yet? – AlwaysLearning May 17 '22 at 13:15
  • it looks like you might be using a linked server, I think that has issues with identity insert. try creating a temp table and populating _that_ from the linked server before inserting into your table. – TZHX May 17 '22 at 13:15

1 Answers1

3

It needs a column list after the INSERT INTO (before the SELECT):

SET IDENTITY_INSERT Thompson_GDM.dbo.employees ON    
GO

INSERT INTO Thompson_GDM.dbo.employees([EmployeeId],    
       [AssignmentId],    
       [more columns...] )    
SELECT [EmployeeId],    
       [AssignmentId],    
       [more columns...]    
FROM TMLAVSQLBIS.ThompsonMachinery.dbo.Employees;    
GO

SET IDENTITY_INSERT Thompson_GDM.dbo.employees OFF    
go

Obviously the "more columns" would be the actual columns

James Casey
  • 2,447
  • 1
  • 11
  • 19