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

>Solution :

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