error when insert into linked server

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

select * into linkservername.mydbname.dbo.tes开发者_运维问答t 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?

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)

The SELECT...INTO [new_table_name] statement supports a maximum of 2 prefixes: [database].[schema].[table]

NOTE: it is more performant to pull the data across the link using SELECT INTO vs. pushing it across using INSERT INTO:

1. SELECT INTO is minimally logged.
2. SELECT INTO does not implicitly start a distributed transaction, typically.

I say typically, in point #2, because in most scenarios a distributed transaction is not created implicitly when using SELECT INTO. If a profiler trace tells you SQL Server is still implicitly creating a distributed transaction, you can SELECT INTO a temp table first, to prevent the implicit distributed transaction, then move the data into your target table from the temp table.

Push vs. Pull Example
In this example we are copying data from [server_a] to [server_b] across a link. This example assumes query execution is possible from both servers:

Push
Instead of connecting to [server_a] and pushing the data to [server_b]:

INSERT INTO [server_b].[database].[schema].[table]
SELECT * FROM [database].[schema].[table]

Pull
Connect to [server_b] and pull the data from [server_a]:

SELECT * INTO [database].[schema].[table]
FROM [server_a].[database].[schema].[table]

I've been struggling with this for the last hour. I now realise that using the syntax

SELECT orderid, orderdate, empid, custid
INTO [linkedserver].[database].[dbo].[table]
FROM Sales.Orders;

does not work with linked servers. You have to go onto your linked server and manually create the table first, then use the following syntax:

INSERT INTO [linkedserver].[database].[dbo].[table]
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE shipcountry = 'UK';

I've experienced the same issue and I've performed the following workaround: If you are able to log on to remote server where you want to insert data with MSSQL or sqlcmd and rebuild your query vice-versa:

so from:

SELECT * INTO linkservername.mydbname.dbo.test 
FROM localdbname.dbo.test

to the following:

SELECT * INTO localdbname.dbo.test
FROM linkservername.mydbname.dbo.test

In my situation it works well.

@2Toad: For sure INSERT INTO is better / more efficient. However for small queries and quick operation SELECT * INTO is more flexible because it creates the table on-the-fly and insert your data immediately, whereas INSERT INTO requires creating a table (auto-ident options and so on) before you carry out your insert operation.

I may be late to the party, but this was the first post I saw when I searched for the 4 part table name insert issue to a linked server. After reading this and a few more posts, I was able to accomplish this by using EXEC with the "AT" argument (for SQL2008+) so that the query is run from the linked server. For example, I had to insert 4M records to a pseudo-temp table on another server, and doing an INSERT-SELECT FROM statement took 10+ minutes. But changing it to the following SELECT-INTO statement, which allows the 4 part table name in the FROM clause, does it in mere seconds (less than 10 seconds in my case).

EXEC ('USE MyDatabase;  
BEGIN TRY DROP TABLE TempID3 END TRY BEGIN CATCH END CATCH; 
SELECT Field1, Field2, Field3 
INTO TempID3 
FROM SourceServer.SourceDatabase.dbo.SourceTable;') AT [DestinationServer]
GO

The query is run on DestinationServer, changes to right database, ensures the table does not already exist, and selects from the SourceServer. Minimally logged, and no fuss. This information may already out there somewhere, but I hope it helps anyone searching for similar issues.