how to import data into oracle ,when primary key conflict ,overwrite old data with new data

Question

how to import data into oracle ,when primary key conflict ,overwrite old data with new data ,just use cmd line ,using imp or impdp ,why oracle doesn't supply the overwrite option

try in command line

Answer 1

If you don't really care about existing data (because newly inserted values should replace existing ones anyway), then see whether IMPDP's TABLE_EXISTS_ACTION which can be set to one of SKIP | APPEND | TRUNCATE | REPLACE helps. See the documentation for more info; I presume you might be interested in truncate or replace option.

---

On the other hand, if target table contains data that doesn't exist in the .dmp file and you'd want to keep it, then you certainly don't want to use what I suggested earlier as you'd lose everything.

In that case, impdp can't do what you want. You'll have to either import data into a temporary table and then write SQL statement(s) which will do what you want - insert new rows, update existing ones.

Alternatively, save existing data into a temporary table, use truncate or replace impdp option and then fix what's wrong.