Fastest Way to Upload Excel Sheet to MSSQL

Would this be an option: http://sqlbeyond.blogspot.nl/2011/09/import-excel-file-to-sql-table-using.html

There's more examples here: http://support.microsoft.com/kb/306397/EN-US

and also an explanation of creating a more permanent solution by defining a linked server to the excel file, which may be helpful.

No, these links do not explain how to properly accomplish sending a sheet tab from Excel VBA to SQL Server in batch form. The first link describes connecting to excel from SSMS or a Stored Procedure, I don't want this initiated server side, but locally, in a local copy of excel, from a sheet in the file, to the SQL server.

I have code already to transfer data line by line, but I need it much much faster in a batch like upload format.

Below is the procedure I currently use to upload data to SQL, it's incredibly slow, I need a way to speed this up, the only way I can imagine would be to initiate a connection to the source and destination in a way that the data could be transferred in a batch-like format. PLEASE any help would be greatly appreciated!!!

Private Sub SQL_TRUNCATE_AND_UPLOAD_TABLE(ServerName, SourceSheet, StartRow, StartCol, LastRow, LastCol, UserID, Password, DatabaseName, DestinationTableName)

    'Important Note: Reference Library: "Microsoft Active X Data Objects 2.X Library" is Required!
    Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    Set RecordSet = New ADODB.RecordSet
    Dim MySheet As Worksheet
    Set MySheet = ThisWorkbook.Sheets(SourceSheet)

    Set Connection = New ADODB.Connection
    Connection.Open "Driver=SQL Server;Server=" & ServerName & ";Database=" & DatabaseName & ";Uid=" & UserID & ";Pwd=" & Password & ";"

    'Clear All Records from the Table
    Connection.Execute "TRUNCATE TABLE " & "[" & DatabaseName & "].dbo." & DestinationTableName

    'Upload all records
    RecordSet.Open DestinationTableName, Connection, adOpenKeyset, adLockOptimistic

    'Loop through all rows on the source sheet
    For RowCounter = StartRow To LastRow

        'Add new row
        RecordSet.AddNew

            'Loop through all columns on row
            For ColCounter = 1 To LastCol

                'Set each metric value (column)
                RecordSet(ColCounter - 1) = MySheet.Cells(RowCounter, ColCounter)

            'Next Column
            Next ColCounter

    'Next Row
    Next RowCounter
   
    'Execute a Batch Update
    RecordSet.UpdateBatch

    'Close All Connections
    RecordSet.Close
    Set RecordSet = Nothing
    Connection.Close
    Set Connection = Nothing

End Sub

I know it's not exactly what you wanted but it _can_ be used from VBA. I have attached a workbook with VBA code based on the links that should be a lot faster than inserting line by line. Note there are a number of settings you need to do but these can be found on the links posted before and also here: http://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
test.xls

I tried to run the code but I'm receiving "Jet.OLEBD.4.0 cannot be used for distributed queries because the producer is configured to run in single-threaded apartment mode."

I think the error may be related to using JET to access xlsx 2007+ excel files?

Also, I tried:

Connection.Execute "SELECT * INTO [DB].[dbo].[TABLE] FROM OpenDataSource('Microsoft.ACE.OLEDB.12.0','Data Source=" & FullFileName & ";Extended Properties=EXCEL 12.0')...[Data$]", , 128

But I receive: The OLE DB provider Microsoft.ACE.OLEDB.12.0 has bot been registered

I do have the 2010 Microsoft Access Database Engine installed so I'm not sure why it's not working :(

Hmm, I have seen some problems with drivers (JET/ACE) when used with 64 bit machine or 64 bit Office. Can you let me know what you're using in terms of hardware/windows/office version?

In the mean time I'll try it out on a .xlsx file because until now I used an old .xls file.

32 Bit WinXP - 32 Bit Office 2010

I can't get it to work with ACE at the moment. I was looking back at the alternative code you posted earlier. The 'FullFileName' I hope is not pointing at the current excel book? The driver needs exclusive access that's the reason for the temporary copy in the code I posted earlier. Could you try saving a temporary copy with .xlsx or .xlsm extension and use that for the import? There are also some extra settings needed in SQL server, the info on excel-sql-server.com seems to have all bases covered.