Jonathan Porter Jonathan Porter - 1 year ago 72
SQL Question

How to create a table using the INSERT INTO clause using linked servers in SQL Server Management Studio

I have a server called

and I would like to create a new table (not already defined) using the
clause onto my local server's reporting database. We have a linked server set up for the
server and our main production server.

Simplified version of current query:

INTO [local].[Reporting].[dbo].[NewTable]
FROM [linked].[Main].[dbo].[Orders]

I'm also getting the error:

The object name 'local.Reporting.dbo.NewTable' contains more than the
maximum number of prefixes. The maximum is 2.

Answer Source

There are two mistakes in your query

1.INTO clause support maximum of 2 prefixes. You cannot include SERVER NAME


2.Unwanted INSERT ketword

So your query should be

INTO [Reporting].[dbo].[NewTable]
FROM [linked].[Main].[dbo].[Orders];
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download