Update existing rows at import
Hi! Is there a tool available that allows me to read data from a comma-delimited flat file into Oracle and if a row already exists, updates that record. If the row does not exist, it should insert a new record into the table. Looks to me like SQL*Loader can't do this. How could this be done? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Update existing rows at import
Helmut, have you thought about using UTL_FILE package? -Original Message- Sent: Wednesday, March 28, 2001 2:37 PM To: Multiple recipients of list ORACLE-L Hi! Is there a tool available that allows me to read data from a comma-delimited flat file into Oracle and if a row already exists, updates that record. If the row does not exist, it should insert a new record into the table. Looks to me like SQL*Loader can't do this. How could this be done? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Update existing rows at import
Can you have a before-insert trigger on your table to do whatever you want and then use conventional sqlldr to load it? winnie "Helmut Daiminger" [EMAIL PROTECTED] on 03/28/2001 12:38:10 PM Please respond to [EMAIL PROTECTED] To: "Oracle DBA List \(Lazy DBA\) \(E-mail\)" [EMAIL PROTECTED], "Oracle List \(Telelist\) \(E-mail\)" [EMAIL PROTECTED], "Joe Sawyer \(E-mail\)" [EMAIL PROTECTED], "Frach Karsten \(E-mail\)" [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Hi! Is there a tool available that allows me to read data from a comma-delimited flat file into Oracle and if a row already exists, updates that record. If the row does not exist, it should insert a new record into the table. Looks to me like SQL*Loader can't do this. How could this be done? This is 8.1.6 on Win2k. Thanks, Helmut Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Update existing rows at import
Helmut, Use SQLLOADER to load the file into a temporary table. Then write a script/procedure to scan the contents of temporary table, compare this info with base table, if row exists, update, else insert. IMME (In my modest eperience) this is the easiest way. HTH Some Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! * This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. * -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Update existing rows at import
You can't query or modify the same table as the trigger is firing on (The old mutating table problem). What you could do is set up a temp table with a before insert trigger which would query the other table and take the appropriate actions, this would solve you having to run a separate procedure after the temp table was loaded. Or load the temp table and run a procedure. When your done with your insert, truncate the temp table and your ready for your next load. [EMAIL PROTECTED] 03/28/01 04:06PM Can you have a before-insert trigger on your table to do whatever you want and then use conventional sqlldr to load it? winnie "Helmut Daiminger" [EMAIL PROTECTED] on 03/28/2001 12:38:10 PM Please respond to [EMAIL PROTECTED] To: "Oracle DBA List \(Lazy DBA\) \(E-mail\)" [EMAIL PROTECTED], "Oracle List \(Telelist\) \(E-mail\)" [EMAIL PROTECTED], "Joe Sawyer \(E-mail\)" [EMAIL PROTECTED], "Frach Karsten \(E-mail\)" [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Hi! Is there a tool available that allows me to read data from a comma-delimited flat file into Oracle and if a row already exists, updates that record. If the row does not exist, it should insert a new record into the table. Looks to me like SQL*Loader can't do this. How could this be done? This is 8.1.6 on Win2k. Thanks, Helmut Think you know someone who can answer the above question? Forward it to them! to unsubscribe, send a blank email to [EMAIL PROTECTED] to subscribe send a blank email to [EMAIL PROTECTED] Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: William Beilstein INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
