question

Z_M avatar image
Z_M asked

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-serveropenquery
10 |1200

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

1 Answer

·
Rob Farley avatar image
Rob Farley answered

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 comments
10 |1200

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

Z_M avatar image Z_M commented ·
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 Likes 0 ·
Rob Farley avatar image Rob Farley commented ·
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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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