Having done this numerous times using cobbled together formulas in Excel I decided to put together a generic template that you can use to carry out updates into your Oracle database with significantly less pain and frustration!
DOWNLOAD THE TEMPLATE
Step 1 - Question the update
Firstly before carrying out the update you should ask yourself and the user providing your data the following questions;
- How long ago was the data in the spreadsheet updated, are other users aware that the update is taking place?
- Is the user/other users aware that data entered into the database since the spreadsheet was created will be overwritten with the data contained in the spreadsheet.
- Is there enough in the spreadsheet to match up to the records correctly (primary key etc)?
Based on that you can then decide whether to proceed with the update.
Step 2 - Format the data
Take the spreadsheet that you have been provided with and make the following amendments;
- Ensure the first column includes one of the fields that will identify the record in the destination table (i,e, personcode, learnerid or staffnumber)
- Check the data to ensure that it is formatted correctly, if the data has been amended manually users may have included spaces etc.
Step 3 - Transpose the data into the template
Paste the data from the spreadsheet your user has provided into the import template in cell B9.
Step 4 - Set the field names, table name and field type
- Enter the table name to be updated in cell C4.
- Identify each column in row 8 by its name in the database.
- Identify each column in row 7 by its type;
- Primary key will be used to construct the where clause
- Update field will be the fields being updated
Step 5 - Review the generated SQL
- The SQL generated as part of the template needs to be reviewed prior to execution.
- Once satisfied that the SQL is correct paste the SQL into your client and execute the code.