Edit Question - KnowledgeHub

Title Body

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?

Tags (comma-separated) Cancel