Update (Oracle) DB with Data from Excel

Question

I have a Oracle table like this: Order_ID, Project_ID.
Order_ID is the primary key.
Project_ID supposed to have a values but left null in many cases.
Now I have a list of Order_ID and Project_ID which is the correct data, but that is in excel.
I have to update my oracle database for matching projects as below:

Bad Data on my Oracle Database:

Order_ID, Project_ID
1000,     20090
1001,     null
1002,     null

Correct Data provided in Excel:

Order_ID, Project_ID
1001,     22565
1002,     25548

Updated Data in Oracle Database should look like:

Order_ID, Project_ID
1000,     20090
1001,     22565
1002,     25548

Please remember I am not looking for the sql statement to do it for each ids/lines, rather imagine I have millions of updates to make and want to automate it with a loop.

I am comfortable to use PL/SQL and Python, but other technologies are also welcome.

Thanks !

Kaushik Nayak · Accepted Answer · 2018-04-20 15:12:42Z

6

There are various approaches to load from a excel. You could convert it to a csv and load via SQL* Loader, Use third party packages to load excel, use external table etc. But, a simple method is to use SQL developer. Here is an article from Jeff Smith that describes how to do it.

Import from Excel

For your convenience, Create a _tmp table with the same structure as that of Orders table and dump the excel onto it.

Create table Orders_tmp as select * FROM Orders where 1=0;

Then use a simple MERGE statement to correct the records in the main Orders table.

MERGE INTO ORDERS t
USING Orders_tmp s
    ON (t.order_id = s.order_id)
WHEN MATCHED
    THEN
        UPDATE
        SET t.project_id = s.project_id;

answered Apr 20, 2018 at 15:12

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Update (Oracle) DB with Data from Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related