error when insert into linked server

sql-server insert linked-server
Daniel Wu picture Daniel Wu · Jan 20, 2011 · Viewed 20.5k times · Source

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

richaux picture richaux · Jan 20, 2011

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)

Related questions

How to insert a row into a linked server table?

I have a server SourceServer I am connected to which has a linked server TargetServer. How should an insert statement looks like (I need to reference Linked server, database, namespace, table): //Connected to [SourceServer] USE [SourceDatabase] DECLARE @HelloWorld NVARCHAR(255) SELECT @…

Read More
Getting an Error on inserting data when using Linked Server Queries

UPDATE : The issue was col1 was hiereachyid type and even a select didnt work for it. Hi all, I am getting this error - Objects exposing columns with CLR types are not allowed in distributed queries. Please use a pass-through …

Read More
Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More