Loading data into database

// This script imports data into temporary table called TEMP_SALES_ORDER,
// then populates the main SALES_ORDER table from the temporary table.
Dim imported_rows, number
Dim old_rows, number
Dim new_rows, number
Dim message, string

// Connect to database
DatabaseConnect( "Sales DB (Oracle 7.2)" )
// Prepare temp.table
DatabaseExecute( "TRUNCATE TABLE scott.temp_sales_order", old_rows )
// Populate temp. table
DatabaseImport( "scott.temp_sales_order", "order.txt", imported_rows )
// Delete from main table matching order to avoid duplicate key problem
DatabaseExecute( "DELETE FROM scott.sales_order WHERE id IN (SELECT id FROM scott.temp_sales_order)", old_rows )
// Populate main table
DatabaseExecute( "INSERT INTO scott.sales_order SELECT * FROM scott.temp_sales_order", imported_rows )
// Disconnect from database and commit transaction
DatabaseDisconnect

// Notify job owner about results
Subtract( imported_rows, old_rows, new_rows )
Concat( "Updated orders: ", old_rows, message)
Concat( message, ", New orders: ", message)
Concat( message, new_rows, message)

MailSend( "Exchange Settings", "pswrd", "sctott@mycompany.com", "Sales Order Update", message )