Jonathan Porter Jonathan Porter - 2 months ago 16
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

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

Simplified version of current query:

SELECT *
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

There are two mistakes in your query

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

DATABASE_NAME.SCHEMA_NAME.TABLE_NAME

2.Unwanted INSERT ketword

So your query should be

SELECT *
INTO [Reporting].[dbo].[NewTable]
FROM [linked].[Main].[dbo].[Orders];