The object name 'AMAPHLINK.Payroll.dbo.EmpResignTb' contains more than the maximum number of prefixes. The maximum is 2

Question

I want to copy my table into AMAPHLINK server, but it keeps giving me an error.

select *
into AMAPHLINK.Payroll.dbo.[EmpResignTb]
from Payroll.dbo.EmpResignTb

Error:

The object name 'AMAPHLINK.Payroll.dbo.EmpResignTb' contains more than the maximum number of prefixes. The maximum is 2

Answer 1

You are getting an error because you are not using a valid name.

The valid syntax is server_name.database_name.schema_name.object_name as referenced on the MSDN article for INSERT.

Remove the incorrect schema and try again.

Solution:

Use square brackets "[]" around the name and remote database server

select *
into [AMAPHLINK].[Payroll].[dbo].[EmpResignTb]
from [Payroll].[dbo].[EmpResignTb]

Answer 2

It appears this can't be done over linked servers.

You could create the table first then do an INSERT INTO.

The same question was asked here: error when insert into linked server