Once data was changed in Excel you may want to synchronise your database with the changes applied in Excel.
The multiple rows update feature in SQL*XL helps you to do this. The way multiple rows update works is as follows:
Use the SQL dialog to retrieve data in Excel that you want to change. E.g. select * from employee where employee_nr >= 1000
Change the data in Excel.
You are allowed to change as many column values as you like.
Do not reorganise the table in any way. Do not sort, delete/add rows/columns.
To nullify a value simply use the delete key in Excel to clear the cell.
To synchronise the database with your changes use the
Update Multiple Rows Dialog of SQL*XL.
To update the data, display the Update Multiple Rows dialog (SQL*XL|Update|Multiple Rows...). This dialog takes 2
settings as input.
Mode of Operation:
Review Only:
Using this mode of operation SQL*XL will visit each changed cell and shows you the previous and current values.
No updates are being made to the database.
Update Only:
This option is the oposite of Review Only. It finds all changed cells and updates the database accordingly. It
will not ask the user to ok each change.
Review & Update:
As a mix
of the 2 options above, this option allows you to go through all the changes
and tell SQL*XL which changes you would like to apply and which
ones you do not want to apply.
Options:
There is currently only 1 option: Commit Each Row.
Use Commit Each Row to save each changed row to the database while SQL*XL goes through all the rows.
Do not use Commit Each Row if you would like to
rollback or commit the changes manually from the transaction menu.
Tips:
Data errors may occur. Most databases have rules to accept data. There are constraints that specify which data
is acceptable. If SQL*XL finds a problem it display an error message to the user. If you use Update Only you will
not be able to stop the update process. Only in the review screen you can press cancel to stop the update process.
I advice to leave the commit each row setting on. Some
databases implement read consistency and therefore keep changes of one user
separate to other users. This only works well if you don't do too many
changes. If you bulk change a lot of data you may find your database
complaining with all the data updated so far being rolled back.