error when insert into linked server

Question

I want to insert some data on the local server into a remote server, and used the following sql:

select * into linkservername.mydbname.dbo.test from localdbname.dbo.test

But it throws the following error

The object name 'linkservername.mydbname.dbo.test' contains more than the maximum number of prefixes. The maximum is 2.

How can I do that?

Answer 1

I don't think the new table created with the INTO clause supports 4 part names. You would need to create the table first, then use INSERT..SELECT to populate it.

(See note in Arguments section on MSDN: reference)