?
Solved

Insert w/Openquery

Posted on 2003-11-17
11
Medium Priority
?
1,228 Views
Last Modified: 2012-08-13
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


0
Comment
Question by:Glass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 9766297
This will create the table on the fly

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


Brett
0
 

Author Comment

by:Glass
ID: 9766359
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
 
LVL 34

Expert Comment

by:arbert
ID: 9766464
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:Glass
ID: 9766566
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
 
LVL 34

Expert Comment

by:arbert
ID: 9766676
"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
 

Author Comment

by:Glass
ID: 9766741
I want to insert the data from the oracle openquery into existing columns.

Glass
0
 

Author Comment

by:Glass
ID: 9766768
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
 
LVL 34

Expert Comment

by:arbert
ID: 9766838
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
 

Author Comment

by:Glass
ID: 9767048
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
 
LVL 34

Accepted Solution

by:
arbert earned 1200 total points
ID: 9767176
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
 

Author Comment

by:Glass
ID: 9770942
You are the Guy!

Thanxs,
Glass
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question