Excel VBA: How to import excel files into MS SQL table? (batch upload, not one record at a time)

Question

Here is basic code structure, which works, but it is very very time consuming (if there are a lot of data in excel file).

How should I re-write the whole program and make it more efficient?

Thanks.

Private Sub CommandButtonTestScoreUpload_Click()  
  
Dim WB As Workbook  
Dim WS As Worksheet  
Dim ShtName As String  
Dim i, j As Long  
Dim WBLastRow As Long  
Dim UploadTestScoreQuery As String  
Dim TestScoreRs As ADODB.Recordset  
Dim sFile As String  
Dim InsertNotUpdate As Boolean  
  
  
  
sFile = Application.GetOpenFilename("*.xl*,*.xl*", , "Select Test Score Excel file")  
  
If sFile <> "False" Then  
    Set WB = Workbooks.Open(sFile)  
    ShtName = WB.Sheets(1).Name  
      
  
    WBLastRow = WB.Sheets(ShtName).Cells(Rows.Count, "A").End(xlUp).Row  
      
    Set CnDev = CreateObject("ADODB.Connection")  
    CnDev.Open ConnectionString  
      
    For i = 2 To WBLastRow  
          
               
        InsertNotUpdate = True  
          
        'I have If...Then... statement here, if a record already exists in Database table, then set InsertNotUpdate = False  
        'Basically, I download whole MS SQL table into another new workbook, then compare excel record against downloaded table.  
          
        If InsertNotUpdate = True Then  'Inert new record  
            UploadTestScoreQuery = "INSERT INTO Test_score ......"   'INSERT SQL Statement  
        Else  'Update existing record  
            UploadTestScoreQuery = "UPDATE Test_score SET ........."   'UPDATE SQL statement  
          
        End If  
          
        Set TestScoreRs = CreateObject("ADODB.Recordset")  
          
        TestScoreRs.Open UploadTestScoreQuery, CnDev  
          
        Set TestScoreRs = Nothing  
      
      
    Next i  
      
End If  
  
Application.DisplayAlerts = False  
WB.Close savechanges:=False  
Application.DisplayAlerts = True  
      
    
Set CnDev = Nothing  
  
MsgBox "We have finished uploading your data."  
  
  
End Sub  
  

Answer 1

SSIS = SQL Server Integration Services
This can import Excel files better than your RBAR.