Is it possible to export a table, make changes in excel and overwrite the data in Oracle using the import data feature? I tried, As it's processing it overwrites all records in the table with each record being processed so that the end result is all records in the table equal the last record in import content.
Yes! There are a few things you need to pay attention to on the import:
- On the "verify mappings and primary key" step, if a key field isn't checked, check one or more manually. Any set of fields that can uniquely identify rows will work. You don't actually need to have a primary key on the table.
- On the last step, choose "Merge" for the import mode. This will cause rows to be inserted if they don't exist in the table, and updated to match the excel file if they do. The key field(s) from step 1 is used to match up the rows to update in the table from the excel file.
- Also on the last step, it sounds like you don't want to truncate or delete rows from your table before import. So make sure those are unchecked.
- One more thing on the last step - choose a commit mode. And remember that depending on your Toad settings, your Editor may be using a different session than the Import Database window. So if you don't commit right away, use the Import Database window to look at your table after the import.
Let me know if any of that is not clear.