Insert w/Openquery

Can download data from Oracle using an Openquery which creates the table once called.  Is it possible to create the table beforehand and have the oracle data insert into a table that has already been created?

If I can create the table then the datatypes will be properly specified.  Otherwise, the initial load may have to be downloaded into a temporary table and then reloaded into the "permanent" table.  Would like to save a step if possible.  Please be specific with the syntax as several variations of the select/insert using openquery have been attempted.  

Thanxs,

Glass


GlassAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
This will create the table on the fly

Select *
into NewTableName
from openquery(oracle,'select * from table)


Brett
0
GlassAuthor Commented:
Hello Arbert,

Do not wish the table to be created on the fly.  Would like to create the table prior to downloading data from oracle.   Is there a way to do this without using temporary tables?

Glass
0
arbertCommented:
Not sure exactly what you're requesting then.


If you want to create a table with just the structure for later use do the same thing only add criteria to the OPENQUERY to just get the structure:

Select *
into NewTableName
from openquery(oracle,'select * from table WHERE 1=0')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

GlassAuthor Commented:
The information downloaded doesn't makeup the entire table.   There are other columns within the table that need to be populated as the table is loading.  So would like to know if can insert the data into an existing table?  Currently the select * into tbl from openquery(oracle, 'select * from table where 1=0')  will generate an error that the table already exists if the table is created prior to the openquery.

Glass

0
arbertCommented:
"There are other columns within the table that need to be populated as the table is loading."


So do you mean the columns need to be created in the existing table, or you want to insert the data into existing columns?
0
GlassAuthor Commented:
I want to insert the data from the oracle openquery into existing columns.

Glass
0
GlassAuthor Commented:
The final table will contain information from three different oracle openquery statements.  If possible would like to create the "final" table and then insert the data using different oracle openquery statements instead of creating three temporary tables and then merging them into a final table.  It may not be possible but maybe it is....

Glass
0
arbertCommented:
So create the one comination table that has the entire structure and use and INSERT INTO statement:



insert into combination table
select  * from openquery(oracle,'select statement1')
go

insert into combination table
select  * from openquery(oracle,'select statement2')
go

insert into combination table
select  * from openquery(oracle,'select statement3')

0
GlassAuthor Commented:
Generates the following error message:

Insert Error:  Column name or number of supplied values does not match table definition.  

The combination table has 10 columns.  The first five are populated with the first oracle openquery.  The second three with the second oracle openquery and the last two with the final oracle openquery.   This error was generated by the first oracle openquery which selects five columns that correlate to the first five columns using the same name that is in the combination table.
0
arbertCommented:
Ok, if you're not going to insert every column, you need to specifically list the columns to insert into.  Also, if you're not going to insert into all the columns, make sure you have the "allow nulls" option.

So, it would look like this:

insert into combinationtable (column1,column2,column3)
select  * from openquery(oracle,'select statement1')
go

insert into combination table (column4,column5,column6)
select  * from openquery(oracle,'select statement2')
go

insert into combination table (column7,column8,column9)
select  * from openquery(oracle,'select statement3')


Brett
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GlassAuthor Commented:
You are the Guy!

Thanxs,
Glass
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.