x

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).

more ▼

asked Mar 04 '10 at 03:49 PM in Default

Z_M gravatar image

Z_M
31 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 04 '10 at 11:43 PM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

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.
Mar 05 '10 at 12:53 PM Z_M
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.
Mar 06 '10 at 12:40 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x101
x26

asked: Mar 04 '10 at 03:49 PM

Seen: 2904 times

Last Updated: Mar 05 '10 at 05:53 AM