Home » SQL & PL/SQL » SQL & PL/SQL » Insert values from Legacy system to Oracle (9.0.4.0)
Insert values from Legacy system to Oracle [message #316950] Mon, 28 April 2008 23:17 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have 2 tables. One is in Oracle and one table in "INFORMIX Server".Both tables has one common column i.e "Deptno".

In Oracle, The table contains the Deptno's 100,200,300,400,500
In Informix the table contains the Deptno's 100,200,300,...... 900,1000.


Now the issue is
I want to insert the records which are in Informix and which 
are not available in Oracle . Means I want to Insert the extra 
records from INFORMIX to Oracle
then Oracle is also having the Deptno's 100,200,300,....,900,1000.



Please give me ideas for this.

Thank you.

[Updated on: Mon, 28 April 2008 23:17]

Report message to a moderator

Re: Insert values from Legacy system to Oracle [message #316951 is a reply to message #316950] Mon, 28 April 2008 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- heterogeneous service
- dump to csv file -> merge external table
...

Regards
Michel
Re: Insert values from Legacy system to Oracle [message #316953 is a reply to message #316951] Mon, 28 April 2008 23:38 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,
I didn't get u.So please give me how to write the statement for this...

Thank you

[Updated on: Tue, 29 April 2008 00:13]

Report message to a moderator

Re: Insert values from Legacy system to Oracle [message #316961 is a reply to message #316953] Tue, 29 April 2008 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I gave you 2 ways, search about them and come back after if you can't get it explaining where you failed.

First, how do you do it if they were in the same database?

Regards
Michel

[Updated on: Tue, 29 April 2008 00:17]

Report message to a moderator

Re: Insert values from Legacy system to Oracle [message #316966 is a reply to message #316961] Tue, 29 April 2008 00:31 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

If they are in the same database I will use "outer join" ..
but From legacy system I have no idea.in SQL*loader how to create .csv file and control file..But here I am not able to write the statement for this..
Re: Insert values from Legacy system to Oracle [message #316969 is a reply to message #316966] Tue, 29 April 2008 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ you don't insert with outer join
2/ if data are in informis and you wantr to insert inside Oracle, you don't use SQL*Loader to create a csv file. SQL*Loader don't know Informix
3/ SQL*Loader don't create anything, SQL*Loader loads.
Please think about what you say.

4/ You didn't try to search about heterogeneous service
5/ You didn't try to search about merge
6/ You didn't try to search about external table

CSV file is the way for the second method I gave.

Regards
Michel
Re: Insert values from Legacy system to Oracle [message #316972 is a reply to message #316969] Tue, 29 April 2008 00:42 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have done in the following way.please look into this

identify the PK's in the Informix table and not on Oracle
Eg:
select pk from tab1@informix 
minux 
select pk from local_table

insert into local_table 
select * from tab1@informix 
where pk in (select pk from tab1@informix 
             minux select pk 
             from local_table)

is it the right way...

[Updated on: Tue, 29 April 2008 00:52] by Moderator

Report message to a moderator

Re: Insert values from Legacy system to Oracle [message #316974 is a reply to message #316972] Tue, 29 April 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep you lines in 80 characters and indent it a little bit (like I did it for you) not put it in a single line.

You already have HS configurated and have a db link on your source database, why don't you say it???
Don't you think it important?

This is one way to do it. You can also use MERGE.

Regards
Michel

[Updated on: Tue, 29 April 2008 00:55]

Report message to a moderator

Re: Insert values from Legacy system to Oracle [message #316988 is a reply to message #316974] Tue, 29 April 2008 01:35 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

How can u give me the idea for this...
i have an idea as
Merge into local_table
using 
informix_table
on(inf.col_name=ora.col_name)
when matched then
update set
inf.col=ora.col
...
...
when not macthed then
insert values(inf.col,inf.col2...)

but I am not getting the required am getting errors in the statement.
Re: Insert values from Legacy system to Oracle [message #316993 is a reply to message #316988] Tue, 29 April 2008 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any error in your post.

Regards
Michel
Re: Insert values from Legacy system to Oracle [message #317025 is a reply to message #316993] Tue, 29 April 2008 04:08 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I wrote the query as following .so please correct this query.
INSERT ALL WHEN TIME NOT IN(1300) THEN INTO CIC_DATA_UPLOAD 
VALUES('1300','17',164,'147',16089,43200,'28-Apr-08','TEST 
VALUES','58.60','20') SELECT * FROM 
CIC_DATA_UPLOAD WHERE UPDATE_DATE='28-apr-08';

one thing I don’t want to upload data. I jus want it to happen live.


Thank you.




[Updated on: Tue, 29 April 2008 04:11]

Report message to a moderator

Re: Insert values from Legacy system to Oracle [message #317027 is a reply to message #316993] Tue, 29 April 2008 04:13 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,
THIS QUERY IS WORKING FINE FOR ME BUT I WANT TO INSERT ONLY ONE ROW, ITS INSERTING THE NO OF ROWS EQUAL TO THE COUNT ON THE LAST SELECT STATEMENT.

INSERT FIRST WHEN TIME NOT IN(1300) THEN INTO CIC_DATA_UPLOAD(TIME,CENTER_NAME) 
VALUES('1300','TEST') SELECT TIME,CENTER_NAME FROM CIC_DATA_UPLOAD WHERE UPDATE_DATE='29-APR-08';



Thanks & Regards,

Re: Insert values from Legacy system to Oracle [message #317036 is a reply to message #317027] Tue, 29 April 2008 05:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't answer to post in UPPER CASE.

Regards
Michel
Re: Insert values from Legacy system to Oracle [message #317043 is a reply to message #317036] Tue, 29 April 2008 05:21 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,

Is there any error in the query? pls don't mind post the answer.. for inserting one row..as per the requirement.

Thank you

[Updated on: Tue, 29 April 2008 05:21]

Report message to a moderator

Previous Topic: SQLTXPLAIN.SQL script
Next Topic: please check the query?
Goto Forum:
  


Current Time: Wed Dec 07 20:03:46 CST 2016

Total time taken to generate the page: 0.06110 seconds