Load data from SQL Server to databricks using Excel VBA

Sub Upload_Data_To_SQLServer_Dynamic() ' Define variables Dim Cn As ADODB.Connection Dim rs As ADODB.Recordset Dim uploadQuery As String Dim batchCounter As Long Dim batchSize As Long Dim batchQuery As String Dim headerArray() As String Dim numColumns As Integer Dim columnList As String Dim sqlQuery As String Dim TableName As String Dim i As Integer Dim StartTime As Double StartTime = Timer ' Establish connection to SQL Server using Windows Authentication Set Cn = New ADODB.Connection Cn.ConnectionString = "Provider=SQLOLEDB;Data Source=YourServerName ;Initial Catalog=mfdb;Integrated Security=SSPI;" Cn.Open ' SQL query to get data from source table (modify as needed) sqlQuery = "SELECT * FROM dbo.tblco_list" ' Execute the query and get a Recordset Set rs = New ADODB.Recordset rs.Open sqlQuery, Cn, adOpenStatic, adLockReadOnly ' Initialize variables batchCounter = 0 batchSize = 80 TableName = "DatabricksDatabasename.tblCoListCon_Temp" uploadQuery = "INSERT INTO " & TableName & " (" ' Read the header row to determine the number of columns numColumns = rs.Fields.Count ReDim headerArray(numColumns - 1) For i = 0 To numColumns - 1 headerArray(i) = rs.Fields(i).Name columnList = columnList & rs.Fields(i).Name If i < numColumns - 1 Then columnList = columnList & ", " End If Next i uploadQuery = uploadQuery & columnList & ") " ' Initialize the batch query batchQuery = "" ' Establish connection to SQL Server (replace details with your own) dsnName = "_DL_PRD_DSN_DBLT" Set Cn2 = New ADODB.Connection Cn2.Open "DSN=" & dsnName & ";Uid=databricksuserid;Pwd=databricksPassword;" ' Read and process the data from the recordset Do While Not rs.EOF ' Build the SELECT part dynamically based on data types (modify as needed) batchQuery = batchQuery & "SELECT " For i = 0 To numColumns - 1 If IsNull(rs.Fields(i).Value) Then batchQuery = batchQuery & "NULL" Else batchQuery = batchQuery & "'" & Replace(rs.Fields(i).Value, "'", "''") & "'" End If If i < numColumns - 1 Then batchQuery = batchQuery & ", " End If Next i batchQuery = batchQuery & " UNION ALL " & Chr(10) batchCounter = batchCounter + 1 ' Check if batch size is reached If batchCounter >= batchSize Then ' Remove the last 'UNION ALL' and execute the batch query If Len(batchQuery) > 0 Then batchQuery = Left(batchQuery, Len(batchQuery) - Len(" UNION ALL " & Chr(10))) Debug.Print uploadQuery & Chr(10) & batchQuery Cn2.Execute uploadQuery & Chr(10) & batchQuery End If ' Reset the batch variables batchQuery = "" batchCounter = 0 End If rs.MoveNext Loop ' Execute any remaining batch query If batchCounter > 0 And Len(batchQuery) > 0 Then batchQuery = Left(batchQuery, Len(batchQuery) - Len(" UNION ALL " & Chr(10))) Cn2.Execute uploadQuery & Chr(10) & batchQuery End If ' Close the recordset rs.Close ' Close the connection Cn2.Close ' Inform user of successful upload MsgBox Format((Timer - StartTime) / 86400, "hh:mm:ss") & " Data upload to SQL Server completed!", vbInformation ' Clean up Set rs = Nothing Set Cn = Nothing End Sub