title: Oracle MERGE from external data source
body: <p>I'm building a .NET application that talks to an Oracle 11g database.  I am trying to take data from Excel files provided by a third party and upsert (<code>UPDATE</code> record if exists, <code>INSERT</code> if not), but am having some trouble with performance. </p>
  
  <p>These Excel files are to replace tariff codes and descriptions, so there are a couple thousand records in each file.  </p>
  
  <pre><code>|   Tariff   |        Description        |
  |----------------------------------------|
  | 1234567890 | 'Sample description here' |
  </code></pre>
  
  <p>I did some research on bulk inserting, and even wrote a function that opens a transaction in the application, executes a bunch of <code>UPDATE</code> or <code>INSERT</code> statements, then commits.  Unfortunately, that takes a long time and prolongs the session between the application and the database.</p>
  
  <pre><code>public void UpsertMultipleRecords(string[] updates, string[] inserts) {
      OleDbConnection conn = new OleDbConnection("connection string here");
      conn.Open();
      OleDbTransaction trans = conn.BeginTransaction();
      try {
          for (int i = 0; i &lt; updates.Length; i++) {
              OleDbCommand cmd = new OleDbCommand(updates[i], conn);
              cmd.Transaction = trans;
              int count = cmd.ExecuteNonQuery();
              if (count &lt; 1) {
                  cmd = new OleDbCommand(inserts[i], conn);
                  cmd.Transaction = trans;
              }
          }
          trans.Commit();
      } catch (OleDbException ex) {
          trans.Rollback();
      } finally {
          conn.Close();
      }
  }
  </code></pre>
  
  <p>I found via Ask Tom that an efficient way of doing something like this is using an Oracle <code>MERGE</code> statement, implemented in 9i. From what I understand, this is only possible using two existing tables in Oracle.  I've tried but don't understand temporary tables or if that's possible.  If I create a new table that just holds my data when I <code>MERGE</code>, I still need a solid way of bulk inserting.</p>