The reason is that the data doesn't match the datatype. I have come across the same issues that I forgot to make the fields match. Though my case is not same as yours, but it shows the similar error message.
The situation is that I copy a table, but accidently I misspell one field, so I change it using the ALTER after creating the database. And the order of fields in both table is not identical. so when I use the INSERT INTO TableName SELECT * FROM TableName, the result showed the similar errors: Operand type clash: datetime is incompatible with uniqueidentifier
This is a simiple example:
use example
go
create table Test1 (
id int primary key,
item uniqueidentifier,
inserted_at datetime
)
go
create table Test2 (
id int primary key,
inserted_at datetime
)
go
alter table Test2 add item uniqueidentifier;
go
--insert into Test1 (id, item, inserted_at) values (1, newid(), getdate()), (2, newid(), getdate());
insert into Test2 select * from Test1;
select * from Test1;
select * from Test2;
The error message is:
Msg 206, Level 16, State 2, Line 24
Operand type clash: uniqueidentifier is incompatible with datetime