Solved

Insert w/Openquery

Posted on 2003-11-17
11
1,215 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 300 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now