current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I would like to write a SQL Update command that could be run once a year to update a record for every account in an Oracle Database based on external values that are received in an excel spreadsheet.

My research thus far is indicating I may be able to use a OPENROWSET command, but most references are showing this used from Excel to MS SQL Server:

INNER JOIN 
  OPENROWSET('Microsoft.Jet.OLEDB.4.0',
             'Excel 8.0;Database=C:\foldername\spreadsheetname.xls;',
             'SELECT column1name, column2name, column3name, column4name
              FROM [worksheetname$]') EXT

Can someone verify I am on the right path or even better provide a basic example?

The basic Psuedo logic is as follows: For every record in the Oracle USER_DEFINED table where the CODE_FIELD is equal to "CRS" AND where I have a Value on the spreadsheet with a matching account number, Update the VALUE field for that record in the Oracle USER_DEFINED table with the contents of the "Value" column in the Spreadsheet.

share|improve this question

1 Answer 1

up vote 0 down vote

Not exactly what you're requesting, but if I were you (and since this is once a year), I would create update statements in Excel using concatenation formulas.

If the first rows/columns of Excel look like this:

ACCT_NBR | NEW_VALUE | CONSTRUCTED_SQL_STMT
123      | Hello     | ="Update USER_DEFINED Set VALUE = '"&B2&"' Where CODE_FIELD='CRS' And Account_Num='"&A2&"';"
456      | World     | ="Update USER_DEFINED Set VALUE = '"&B3&"' Where CODE_FIELD='CRS' And Account_Num='"&A3&"';"

Then just run copy/paste the resulting series of update statements into SQL*Plus. Any that don't have a match in your DB will not trigger an update, and any that do match will get updated.

Do a commit at the end and you're done!

share|improve this answer
    
Interesting option, but I am looking for something I can create a macro for and just have a non-technical expert run according to a schedule. Thanks for the suggestion though. –  Nicole Cook Mar 10 '12 at 18:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.