Le moyen le plus rapide de transférer des données de table Excel sur SQL 2008R2

Le code suivant transférera les milliers de données en quelques secondes seulement (2-3 secondes).

Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Sheets("DataSheet")        

    Dim Con As Object
    Dim cmd As Object
    Dim ServerName As String
    Dim level As Long
    Dim arr As Variant
    Dim row As Long
    Dim rowCount As Long

    Set Con = CreateObject("ADODB.Connection")
    Set cmd = CreateObject("ADODB.Command")

    ServerName = "192.164.1.11" 

    'Creating a connection
    Con.ConnectionString = "Provider=SQLOLEDB;" & _
                                    "Data Source=" & ServerName & ";" & _
                                    "Initial Catalog=Adventure;" & _
                                    "UID=sa; PWD=123;"

    'Setting provider Name
     Con.Provider = "Microsoft.JET.OLEDB.12.0"

    'Opening connection
     Con.Open                

    cmd.CommandType = 1             ' adCmdText

    Dim Rst As Object
    Set Rst = CreateObject("ADODB.Recordset")
    Table = "EmployeeDetails" 'This should be same as the database table name.
    With Rst
        Set .ActiveConnection = Con
        .Source = "SELECT * FROM " & Table
        .CursorLocation = 3         ' adUseClient
        .LockType = 4               ' adLockBatchOptimistic
        .CursorType = 0             ' adOpenForwardOnly
        .Open

        Dim tableFields(200) As Integer
        Dim rangeFields(200) As Integer

        Dim exportFieldsCount As Integer
        exportFieldsCount = 0

        Dim col As Integer
        Dim index As Integer
        index = 1

        For col = 1 To .Fields.Count
            exportFieldsCount = exportFieldsCount + 1
            tableFields(exportFieldsCount) = col
            rangeFields(exportFieldsCount) = index
            index = index + 1
        Next

        If exportFieldsCount = 0 Then
            ExportRangeToSQL = 1
            GoTo ConnectionEnd
        End If            

        endRow = ThisWorkbook.Sheets("DataSheet").Range("A65536").End(xlUp).row 'LastRow with the data.
        arr = ThisWorkbook.Sheets("DataSheet").Range("A1:CE" & endRow).Value 'This range selection column count should be same as database table column count.

        rowCount = UBound(arr, 1)            

        Dim val As Variant

        For row = 1 To rowCount
            .AddNew
            For col = 1 To exportFieldsCount
                val = arr(row, rangeFields(col))
                    .Fields(tableFields(col - 1)) = val
            Next
        Next

        .UpdateBatch
    End With

    flag = True

    'Closing RecordSet.
     If Rst.State = 1 Then
       Rst.Close
    End If

   'Closing Connection Object.
    If Con.State = 1 Then
      Con.Close
    End If

'Setting empty for the RecordSet & Connection Objects
Set Rst = Nothing
Set Con = Nothing
End Sub

fonctionne assez bien, d'autre part pour améliorer la vitesse, nous pouvons toujours modifier la requête:

à la place: source = "Sélectionnez * à partir de" & Table

Nous pouvons utiliser: source = "Sélectionnez le top 1 * à partir de" & Table

Voici nous n'avons besoin que de noms de colonnes. Donc, pas besoin de maka une requête pour une table entière, qui prolonge le processus tant que de nouvelles données importées.