title: Fastest way to transfer Excel table data to SQL 2008R2
body: <p>Does anyone know the fastest way to get data from and Excel table (VBA Array) to a table on SQL 2008 <em>without</em> using an external utility (i.e. bcp)?
  Keep in mind my datasets are usually 6500-15000 rows, and about 150-250 columns; and I end up transferring about 20-150 of them during an automated VBA batch script.</p>
  
  <p>I have tried several methods for getting large amounts of data from an Excel table (VBA) to SQL 2008.  I have listed those below:</p>
  
  <p>Method 1.  Pass table into VBA Array and send to stored procedure (ADO)
  -- Sending to SQL is SLOW</p>
  
  <p>Method 2.  Create disconnected RecordSet load it, then sync. 
  -- Sending to SQL VERY SLOW</p>
  
  <p>Method 3.  Put table into VBA array, loop though the array and concatenate(using delimiters) then send to stored procedure.
  -- Sending to SQL SLOW, but faster than Method 1 or 2.</p>
  
  <p>Method 4. Put table into VBA array, loop though the array and concatenate(using delimiters) then place each row with ADO recordset .addnew command.
  --Sending to SQL very FAST (about 20 times faster than methods 1-3), but now I will need to split that data using a separate procedure, which will add significant wait time.</p>
  
  <p>Method 5. Put table in VBA array, serialize into XML, send to stored procedure as VARCHAR and specify XML in stored procedure.
  --Sending to SQL INCREDIBLY SLOW (about 100 times slower than methods 1 or 2)</p>
  
  <p>Anything I am missing?</p>