You're trying to use a five-part identifier for the column, which won't work. Try adding an alias to the table.
Also, the
INNER JOIN
within the
NOT IN
nested query doesn't make any sense to me. You're already selecting from server1, so you don't need to join to it again in the nested query.
INSERT INTO [server2].[RemedyIntegration].[dbo].[tbl_Insert_Data]
SELECT * FROM [server1].[ABC_Test_Link_Vin].[dbo].[tbl_Insert_Data] As S
WHERE S.ID NOT IN
(
SELECT I.ID
FROM [server2].[RemedyIntegration].[dbo].[tbl_Insert_Data] As I
)
Or:
INSERT INTO [server2].[RemedyIntegration].[dbo].[tbl_Insert_Data]
SELECT * FROM [server1].[ABC_Test_Link_Vin].[dbo].[tbl_Insert_Data] As S
WHERE NOT EXISTS
(
SELECT I.ID
FROM [server2].[RemedyIntegration].[dbo].[tbl_Insert_Data] As I
WHERE I.ID = S.ID
)