How to export Excel data from different sheets to SQL-SERVER Database?

I am new in Excel VBA and SQL. I have managed to create a macro button and push Excel cell data to SQL server tables. However, I am a bit puzzled:

How can I take Excel cell data from different sheets and then push them to different tables in SQL Server database? (Currently, I have 3 sheets – Customers, Test, Information – in one Excel file.)

Current working code:

Sub Button1_Click()
 
Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim sCustomerId, sFirstName, sLastName As String

With Sheets("Customers")
        
    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=TESTpcSQLEXPRESS;Initial Catalog=ExcelSQLServerDemo;Trusted_connection=yes"
        
   'Skip the header row
    iRowNo = 2
        
    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        sCustomerId = .Cells(iRowNo, 1)
        sFirstName = .Cells(iRowNo, 2)
        sLastName = .Cells(iRowNo, 3)
            
        'Generate and execute sql statement to import the excel rows to SQL Server table
        conn.Execute "INSERT into dbo.Customers (CustomerId, FirstName, LastName) values ('" & sCustomerId & "', '" & sFirstName & "', '" & sLastName & "')"

        iRowNo = iRowNo + 1
    Loop
    
    MsgBox "Customers Exported To Database"
        
    conn.Close
    Set conn = Nothing
    
    End With
End Sub

Do I need to store the data in arrays and then push them to the database?

Answer

You shouldn’t use insert queries for every row you want to export. Instead, if you want to do it manually, open a recordset:

Sub Button1_Click()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim iRowNo As Integer
Dim sCustomerId, sFirstName, sLastName As String

With Sheets("Customers")

    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=TESTpcSQLEXPRESS;Initial Catalog=ExcelSQLServerDemo;Trusted_connection=yes"
    conn.CursorLocation = adUseClient 'Use a client-side cursor
    rs.Open "SELECT * FROM dbo.Customers", conn, adOpenDynamic, adLockOptimistic 'Open the table into a recordset

   'Skip the header row
    iRowNo = 2

    'Loop until empty cell in CustomerId
    Do Until .Cells(iRowNo, 1) = ""
        rs.AddNew 'Add a new row
        rs!CustomerId = .Cells(iRowNo, 1) 'Set row values
        rs!FirstName = .Cells(iRowNo, 2)
        rs!LastName = .Cells(iRowNo, 3)
        rs.Update 'Commit changes to database, you can try running this once, or once every X rows
        iRowNo = iRowNo + 1
    Loop

    MsgBox "Customers Exported To Database"

    conn.Close
    Set conn = Nothing

    End With
End Sub

This has several advantages, including but not limited to increased performance, the ability to insert quoted values and increased stability.