Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Inserting one Excel row into multiple tables (Read 2997 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Inserting one Excel row into multiple tables
12.09.06 at 10:19:06
Inserting one Excel row into multiple database tables
Lisa from the USA asked the following question.
The primary requirement I need to address is an ability to insert a record into multiple Oracle tables from a single Excel record.  I have Excel files with data (in single records) that will be used to populate multiple Oracle tables. The number of records per file varies.  Maybe you can tell me the best way to handle this with SQL*XL.
There are a number of ways you can tackle this. Much depends on the complexity of the tables.
  • Run the insert process multiple times
    This may be an open door...
    You can run SQL*XL's multiple rows insert feature for all the tables that need to receive the data.
  • Use a temporary table inside the database
    Load the data into a temporary database table. Then use SQL insert commands to insert the data from this temporary table into the target tables. If this is a complex procedure you can write PL/SQL (of T-SQL if you use SQL Server) code to do this record by record.

In general, if the insert process is not very complex and you just need to insert some data into certain tables you can use the first approach. If you also need to update tables etc it can be handy to retrieve the data to update back from the database, use Excel's lookup functions to modify the returned data from the data in Excel with formulas with the new values and then to use the multiple rows update feature to update the database.
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1