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)

Answer 2

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]