Solved

Insert w/Openquery

Posted on 2003-11-17
11
1,227 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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 …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 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