How to create a table from a linked server into the local machine-CodePudding

I need to copy tables from a linked server onto my local machine. I am working in SQL management studio. The linked server is Oracle based. My end goal is to set up a stored proc that deletes a table if it exists and creates a new table in its place with refreshed data. This will be done for many tables as needed. The issue with the below code is that I get the error:

Incorrect syntax near the keyword 'SELECT'.

I am stuck at creating the table.

CREATE TABLE test AS

SELECT DUMMY 

FROM OPENQUERY (LServer, '

Select * 
from sourceT

');

The data in the dummy table is just one column with a single value "x". I have seen posts that suggest using a certain notation in naming the linked server table, like <server.database.schema.tablename> but this doesn't seem to work,even if I just run the select statement using the openquery. If I just run the select part in the script above, this does work.

CodePudding user response：

CREATE TABLE test AS

Is valid in Oracle but not SQL Server

You want

-- if the table already exists drop it
DROP TABLE IF EXISTS test;

-- now create a table and load into it
SELECT DUMMY 
INTO test
FROM OPENQUERY (LServer, '
Select * 
from sourceT')