Solved

Insert w/Openquery

Posted on 2003-11-17
11
1,217 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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 …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

920 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

15 Experts available now in Live!

Get 1:1 Help Now