Updating a database with new records using separate INSERT commands for each new record can sometimes take very long time, especially if there are many thousands of new records to insert. One way to speed up this process can be to use the BULK INSERT command to update the SQL database in a significantly quicker way, e.g. in 1 minute instead of 20 minutes when inserting each record separately (100 000 records).

With the function below you can upload a tab-separated text file (without headings) to an SQL data table very quickly compared to using separate SQL insert statements for each record. The source file should be in Unicode format if it contains non-English characters. Click here for information on how to create a text file in Unicode format.

After creating the tab-separated text file with the new data for the SQL server, copy the file to a folder on the SQL server, e.g. using FTP. You might also need to contact the database administrator and ask to be granted permission to run the BULK INSERT command.
Then you can use the function below to bulk load the contents into the database.
Note! You have to edit the 3 constants in the function below before using it.

Function PerformBulkUpload(cn As ADODB.Connection, strSourceFileName As String, Optional blnTruncateTable As Boolean = False) As Boolean
' strSourceFileName must be the full pathname to the file on the SQL server, e.g.: F:\FTP\USERID\NewUploadedData.txt
Const cstrDataBaseName As String = "MYDATABASE"
Const cstrDataBaseUser As String = "dbo"
Const cstrDataBaseTable As String = "MyTableName"
Dim strSQL As String, lngCount As Long
    PerformBulkUpload = False
    If cn Is Nothing Then Exit Function
    If Len(strSourceFileName) < 6 Then Exit Function

    If cn.State = adStateOpen Then
        PerformBulkUpload = True
        With cn
            If blnTruncateTable Then
                strSQL = "truncate table " & strTargetTable
                .Execute strSQL, , adCmdText
            End If
            If Len(strSourceFileName) > 0 Then
                strSQL = "bulk insert [" & cstrDataBaseName & "].[" & cstrDataBaseUser & "].[" & cstrDataBaseTable & "] "
                strSQL = strSQL & "from '" & strSourceFileName & "' "
                strSQL = strSQL & "with (DATAFILETYPE = 'widechar', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')"
                On Error GoTo BulkLoadError
                .Execute strSQL, lngCount, adCmdText
                On Error GoTo 0
            End If
            .Close
        End With
    End If
    Exit Function

BulkLoadError:
    MsgBox Err.Description
    PerformBulkUpload = False
    Resume Next
End Function