Trying to adjust my VBA Macro for inserting batches of rows into an SQL Server

Question

I have already managed to program this VBA macro which takes as an input a range within an excel sheet, iterates over its columns and rows and then inserts the values with a SQL statement into a server database. But since some files which I need to insert into the database have over 90.000 rows I need to implement a batch insert inside my for loop, so that every iteration only takes a set number of rows for the insert procedure (like for example 500 rows at a time).

Below you find the code of the Macro which works just fine with smaller datasets:

Function RangeToInsert(rRng As Range) As String

   Dim vaData As Variant
   Dim i As Long, j As Long
   Dim aReturn() As String
   Dim aCols() As String
   Dim aVals() As Variant

   Const sINSERT As String = "INSERT INTO dbo.ExcelTest2"
   Const sVAL As String = " VALUES "

   'Read in data
   vaData = rRng.Value

   'Create arrays
   ReDim aReturn(1 To UBound(vaData))
   ReDim aCols(1 To UBound(vaData, 2))
   ReDim aVals(1 To UBound(vaData, 2))

   'Fill column name array from first row
   For j = LBound(vaData, 2) To UBound(vaData, 2)
       aCols(j) = "[" & vaData(1, j) & "]"
   Next j

   'Go through the rest of the rows
   For i = LBound(vaData, 1) + 1 To UBound(vaData, 1)

       'Fill a temporary array
       For j = LBound(vaData, 2) To UBound(vaData, 2)
           aVals(j) = "'" & vaData(i, j) & "'":
       Next j

       'Build the string into the main array
       aReturn(i) = sINSERT & "(" & Join(aCols, ",") & ")" & sVAL & "(" & Join(aVals, ",") & ");"
   Next i

   RangeToInsert = Join(aReturn, vbNewLine)

End Function

Sub Test_Export()
   
   Dim sConnString As String
   Dim tbl As ListObject
   Dim column_count, row_count As Integer
   Dim Headers As Variant
   
   sConnString = "Provider=X;Data Source=X;Initial Catalog = X;Integrated Security=SSPI"
   Dim conn As ADODB.Connection
   Dim rs As ADODB.Recordset
   Set conn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   conn.Open sConnString
   conn.Execute ("use Salesreport")
   conn.Execute ("truncate table dbo.ExcelTest2")
   
   conn.Execute (RangeToInsert(ActiveSheet.Range("A1:AC5000")))

End Sub

For the batching I thought about adding an if clause along those lines:

If (i Mod 500) = 0 Then
                aReturn(i) = sINSERT & "(" & Join(aCols, ",") & ")" & sVAL & "(" & Join(aVals, ",") & ");"
                RangeToInsert = Join(aReturn, vbNewLine)
                RangeToInsert = RangeToInsert As String
                conn.Execute (RangeToInsert)
End If

But to be honest I am a little bit stuck right now on how I can actually implement this clause into the for loop while also making sure that still every row of the dataset gets inserted in the end. I would be really greatful for every hint or help.

Answer 1

This sounds like a weird design. Maybe you need to re-think your setup. You can certainly use VBA, like you are doing, or do a bulk insert into SQL Server, from a CSV. Here's a link with some info:

https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver15

There are resize functions and offset functions that you may be able to leverage, but anytime you use 'select' things are going to run slow.

https://www.microsoft.com/en-us/microsoft-365/blog/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel/

You can even save the XL file as a tab-delimited text file and do this...

BULK INSERT TableName
        FROM 'C:\your_file_path.txt'
            WITH
    (
                FIELDTERMINATOR = '\t',
                ROWTERMINATOR = '\n'
    )
GO

https://dbtut.com/index.php/2019/05/13/bulk-insert-in-sql-server/

Also, Python offers an easy way to chunk data and do multiple inserts.

https://towardsdatascience.com/loading-large-datasets-in-pandas-11bdddd36f7b

Although, maybe Python is outside the realm of feasibility for you. Just offering a couple ideas here.

Answer 2

You can have as many text fields as you want, you need to be in the shape text editor (hit F2 first so that text edit is opened, and then insert fields). Here is a video. Please note that this is not a programming question, probably should be closed here as offtopic.