0

I am still learning Oracle SQL, and I've been trying to find the best way to update/insert records in an OracleSQL table with the data from a CSV file.

So far, I've figured out how to load the csv into a temporary table using External Tables in Oracle, but I'm having difficulty finding a detailed guide on how to update/insert (UPSERT) the loaded data into an existing table.

What is the best way to do this, when I have 30+ fields in the table? For example, is it best to read the csv line by line with something like pandas and update each record one by one, or is it best to do it with a sql script using something like a merge statement? Not all records in the csv have a value for the primary key, in which case I need to insert rather than update. Thanks for the help!

Improve this question
1

1 Answer 1

Reset to default
1

That looks like a MERGE, indeed.

Data from external table would then be used to

  • update values in existing rows
  • create new rows in the target table

Pandas and row-by-row processing? I wouldn't do that. If you already have a powerful database, then use its capabilities. Row-by-row is usually slow-by-slow and there's rarely some benefit in doing it that way.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

MERGE ftw! Executing data manipulation in the database will always be more performative than moving it in and out of the database
Oracle has a very good utility called SqlLoader to parse and fast-load text files (fixed format, or delimited: CSV Tab separated, etc.); have you considered?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.