Any way to improve the speed of this VBA loop to insert data into SQL?
<p>I have a VBA loop that inserts around 120,000 rows into a SQL table.</p>

<p>I have read that using a loop for large datasets is quite time consuming (which I've found, this takes over 10 minutes to complete)</p>

<p>If someone could suggest something to me to maybe bulk insert this data as a range or something?</p>

<p>Thanks guys!</p>

<pre><code>Sub export_to_sql()

Dim conn As New ADODB.Connection
Dim RowNo As Long
Dim C1, C2, C3, C4 As String
Dim wbk As Workbook
Dim strFile As Variant
Dim shtname As String
ChDir "xxx"
strFile = Application.GetOpenFilename
If strFile = False Then
Exit Sub
Else: End If



    ' *** Open workbook first ***
    Set wbk = Workbooks.Open(strFile)
    shtname = ActiveWorkbook.Worksheets(1).Name
    lastrow = wbk.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
    With Sheets(shtname)

    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=SERVER\SQL2008R2;Initial Catalog=xxxxx;User ID=xx; Password=xxxxxxxx; "

    ' Delete table data
    conn.Execute "DELETE FROM EcommerceTablename"

    'Skip the header row
    RowNo = 2

    'Loop until empty cell in Col 1
    Do Until .Cells(RowNo, 1) = ""
        C1 = .Cells(RowNo, 1)
        C2 = .Cells(RowNo, 2)
        C3 = .Cells(RowNo, 3)
        C4 = .Cells(RowNo, 4)

        'Generate and execute sql statement to import the excel rows to SQL Server table

        conn.Execute "insert into EcommerceTablename ([Col_1_C], [Col_2_C], [Col_3_C], [Col_4_C]) values ('" &amp; C1 &amp; "', '" &amp; C2 &amp; "', '" &amp; C3 &amp; "', '" &amp; C4 &amp; "')"

        RowNo = RowNo + 1
    Loop


    conn.Close
    Set conn = Nothing

End With

End Sub
</code></pre>