Самый быстрый способ переноса данных таблицы Excel в SQL 2008R2

Не существует единственно быстрого способа, поскольку он зависит от ряда факторов. Убедитесь, что индексы в SQL настроены и оптимизированы. Большое количество индексов снизит производительность вставки / обновления, поскольку каждая вставка должна будет обновлять индекс. Убедитесь, что вы устанавливаете только одно соединение с базой данных и не открываете / не закрываете ее во время операции. Запускайте обновление при минимальной нагрузке на сервер. Единственный другой метод, который вы не пробовали, - это использовать объект ADO Command и выполнить прямую инструкцию INSERT. При использовании метода «AddNew» объекта набора записей обязательно введите только одну команду «UpdateBatch» в конце вставок. Если не считать этого, VBA может работать только с той скоростью, с которой сервер SQL принимает входные данные.

РЕДАКТИРОВАТЬ: Похоже, вы все перепробовали. В SQL Server есть также так называемый режим восстановления с неполным протоколированием, который снижает накладные расходы на запись в журнал транзакций. Может быть, стоит посмотреть. Это может быть проблематично, поскольку требует немного поработать с моделью восстановления базы данных, но это может быть полезно для вас.

Следующий код передаст тысячи данных всего за несколько секунд (2-3 секунды).

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

Безусловно, самый быстрый способ сделать это - использовать BULK INSERT T-SQL.

Есть несколько предостережений.

• Скорее всего, вам сначала потребуется экспортировать свои данные в CSV (вы можете импортировать напрямую из Excel; мой опыт заключается в переходе с Access .mdbs на SQL Server, что требует промежуточного шага в CSV) .
• Компьютер с SQL Server должен иметь доступ к этому CSV (, когда вы запускаете команду BULK INSERT и указываете имя файла, помните, что имя файла будет разрешено на машине, на которой запущен SQL Server).
• Возможно, вам придется настроить значения по умолчанию FIELDTERMINATOR и ROWTERMINATOR, чтобы они соответствовали вашему CSV.

Мне потребовались некоторые методы проб и ошибок, чтобы настроить эту настройку изначально, но повышение производительности было феноменальным по сравнению с любыми другими методами, которые я пробовал.

работает довольно хорошо, с другой стороны, для повышения скорости мы все же можем изменить запрос:

Вместо этого: Source = "SELECT * FROM " & Table

Мы можем использовать: Source = "SELECT TOP 1 * FROM " & Table

Здесь нам нужны только имена столбцов. Таким образом, нет необходимости создавать запрос для всей таблицы, которая расширяет процесс до тех пор, пока импортируются новые данные.

Насколько я помню, вы можете создать связанный сервер с файлом Excel (если сервер может найти путь; лучше всего поместить файл на локальный диск сервера), а затем использовать SQL для извлечения данных из него.

Попробовав несколько методов, я вернулся к относительно простому, но быстрому. Это быстро, потому что заставляет SQL-сервер выполнять всю работу, включая эффективный план выполнения.

Я просто создаю длинную строку, содержащую сценарий операторов INSERT.

    Public Sub Upload()
        Const Tbl As String = "YourTbl"
        Dim InsertQuery As String, xlRow As Long, xlCol As Integer
        Dim DBconnection As New ADODB.Connection

        DBconnection.Open "Provider=SQLOLEDB.1;Password=MyPassword" & _
            ";Persist Security Info=false;User ID=MyUserID" & _
            ";Initial Catalog=MyDB;Data Source=MyServer"

        InsertQuery = ""
        xlRow = 2
        While Cells(xlRow, 1) <> ""
            InsertQuery = InsertQuery & "INSERT INTO " & Tbl & " VALUES('"

            For xlCol = 1 To 6 'Must match the table structure
                InsertQuery = InsertQuery & Replace(Cells(xlRow, xlCol), "'", "''") & "', '"  'Includes mitigation for apostrophes in the data
            Next xlCol
            InsertQuery = InsertQuery & Format(Now(), "M/D/YYYY") & "')" & vbCrLf 'The last column is a date stamp, either way, don't forget to close that parenthesis
            xlRow = xlRow + 1
        Wend

        DBconnection.Execute InsertQuery 'I'll leave any error trapping to you
        DBconnection.Close  'But do be tidy :-)
        Set DBconnection = Nothing
    End Sub