Linker IT Software
Order Home
menubar-top-links menubar-top-rechts

SQL*XL: Database to Excel bridge

Related LIT software: litLIB: Excel power functions pack encOffice: Protect your Excel file easy and safe

Home Products SQL*XL Help Features

Buy now

Download now

SQL*XL online help

 

Update: Send updated data from Excel back to the database

Update: Send updated data from Excel back to the database

SQL*XL
home

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.

See also:

SQL*XL ribbon in Excel