Fastest way to transfer Excel table data to SQL 2008R2

Question

Does anyone know the fastest way to get data from and Excel table (VBA Array) to a table on SQL 2008 without 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.

I have tried several methods for getting large amounts of data from an Excel table (VBA) to SQL 2008. I have listed those below:

Method 1. Pass table into VBA Array and send to stored procedure (ADO) -- Sending to SQL is SLOW

Method 2. Create disconnected RecordSet load it, then sync. -- Sending to SQL VERY SLOW

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.

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.

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)

Anything I am missing?

Answer 1

There is no single fastest way, as it's dependent on a number of factors. Make sure the indexes in SQL are configured and optimized. Lots of indexes will kill insert/update performance since each insert will need to update the index. Make sure you only make one connection to the database, and do not open/close it during the operation. Run the update when the server is under minimal load. The only other method you haven't tried is to use a ADO Command object, and issue a direct INSERT statement. When using the 'AddNew' Method of the recordset object, be sure to issue only one 'UpdateBatch' Command at the end of the inserts. Short of that, the VBA can only run as fast as the SQL server accepting the inputs.

EDIT: Seems like you've tried everything. There is also what is known as 'Bulk-Logged' recovery mode in SQL Server, that reduces the overhead of writting so much to the transaction log. Might be something worth looking into. It can be troublesome since it requires fiddling with the database recovery model a bit, but it could be useful for you.