Can I create a new table using 'select into new table' on a linked server?

Question

question

Z_M asked Mar 4, '10

Can I create a new table using 'select into new table' on a linked server?

I have an Oracle linked server created on my SQL Server 2008 and it works fine for selecting from an existing table on the linked server using openquery. (The four-part naming does not work - I get "the provider does not expose the necessary interfaces to use a catalog or schema")

I can also do openquery statements for inserting into existing tables on the linked server. But now I want to create a new table - a copy of a local table - on the linked server. Can I use openquery syntax for this? Would it work? Something like:

select * into --- openquery syntax (LNKDSRVR, 'new_table') ---
from local_table;

(The reverse works fine).

linked-server openquery

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-04T23:43:27Z

Rob Farley answered Mar 4, '10

I doubt it, because the hook into Oracle won't support pushing the data in like that. As the error says, the necessary interface bits aren't there.

Try from the other side though - see if there's an Oracle method to hook into SQL.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Z_M commented · Mar 05, 2010 at 12:53 PM

Why then does this work through the same interface: insert into openquery(LNKDSRVR,'select all_columns from EXISTING_EMPTY_TABLE') select 'all_columns' from local_table_with_data; It populates linked server table fine. I just wanted to avoid the hassle of creating a dozen tables with the necessary structure first, then using the above syntax to populate them. 'select * into' would have saved so much work.

0 ·

Rob Farley commented · Mar 06, 2010 at 12:40 AM

Because this is understood by the interface as an insert into that subquery, which is valid. SELECT...INTO doesn't have a resultset to use as the destination, which would therefore require the interface to know that a table must be created and how to do that, which it doesn't.

0 ·

question