Home » RDBMS Server » Server Utilities » Updating fields with SQL Loader
Updating fields with SQL Loader [message #368145] Mon, 11 September 2000 03:16 Go to next message
Didier Martineau
Messages: 1
Registered: September 2000
Junior Member
Hello,

I have a massive set of data to update into a table (or add when a given entry doesn't exist).

Is there a possibility with SQL Loader to perform update operations ?

For example:
table : MyTest
rows : customerID(*), first_name, last_name, address, account
(*)= primary key

Everyday, I have a set of data (giving me "customerID" and "account" fields only) for which I'd like to either modify the "account" field (for entries which already exist) or add another entry when there is a new "customerID".

My first tests (with the command REPLACE) delete the old entries and create new ones, which is, I guess the usual purpose for 'REPLACE'. Of course, all the informations about the "first_name", "last_name" and "address" are deleted (sob...).

Any help ?

Thanks in advance,

Didier

Hello,

I have a massive set of data to update into a table (or add when a given entry doesn't exist).

Is there a possibility with SQL Loader to perform update operations ?

For example:
table : MyTest
rows : customerID(*), first_name, last_name, address, account
(*)= primary key

Everyday, I have a set of data (giving me "customerID" and "account" fields only) for which I'd like to either modify the "account" field (for entries which already exist) or add another entry when there is a new "customerID".

My first tests (with the command REPLACE) delete the old entries and create new ones, which is, I guess the usual purpose for 'REPLACE'.

Any help ?

Thanks in advance,

Didier
Re: Updating fields with SQL Loader [message #368147 is a reply to message #368145] Tue, 12 September 2000 16:55 Go to previous message
Bala
Messages: 205
Registered: November 1999
Senior Member
You can use this method.
create a temp table as

create table tab_temp (customerID(*) varchar2(), first_name varchar2(), last_name varchar2(), address varchar2(), account number);

And using sqlldr load this tab_temp.
you can use direct=true to speed up the data load.

After that, use two separate sqls one for insert and one for update. Like this.

--insert.sql
insert into tab_perm
(select * from tab_temp where customerID not in(
select customerID from tab_perm));

--update.sql
update tab_perm a set account =
(select account from table tab_temp b
where a.customerID = b.customerID);

And truncate the tab_temp table every time
before daily load.

You can put all these in one batch file and schedule it to run daily.

Good luck
Bala
Previous Topic: "\n" in datafile causing problems
Next Topic: Re: RMAN and Omniback
Goto Forum:
  


Current Time: Sat May 21 03:42:26 CDT 2022