| Updating fields with SQL Loader [message #368145] |
Mon, 11 September 2000 03:16  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
