How to "vba bulk insert sql server"?

Please see the following code for Exporting data from Excel to SQL.

Step 1 before inserting this code into you VBE (Alt + F11) is to enable the library reference for Outlook so that you have access to Outlook classes for the below code.

Press (Alt + F11) keys, Go to Tools, References, and make sure "Microsoft ActiveX Data Objects 6.1 Library". if you have an earlier or later version than 6.1 that should be fine as well.

How my worksheet looks like

How my Database table looks like after the import

Code

Sub ExportToSQL()
 
SQLsheet.Activate
​
'Turns off screenupdating for faster script.
Application.ScreenUpdating = False 
​
'Imports the "SQL" class and library references called ActiveX Data Objects Library.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
​
'Create the connection string to our server.
sConnString = "Provider=SQLOLEDB;" & _
              "Data Source=Servername;" & _
              "Initial Catalog=Database;" & _
              "Trusted_connection=yes;"
​
'Provider = Fix variable.
'Data Source = Type in your servername.
'Initial Catalog = Type in your database name.
​
'2 options of connecting to the server.
'1) "Trusted_connection=yes;" = This refers to Windows authentication setting in your database.
'2) "User Id=myUsername;Password=myPassword;" = If you are not using Windows authentication, you need to specify your username and password.
​
'Specify import range to loop.
Dim Rng As Range
EndPlace = Range("A3").End(xlDown).End(xlToRight).Address(False, False)
Set Rng = Application.Range("A3:" & EndPlace)
Dim Row As Range

'Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
​
'Setting the name of my SQL table.
MyTable = "TestTable" 'Change this to your SQL table name.
​
'Open the connection and execute.
conn.Open sConnString
​
'*********************Inserts new data*********************
'Insert new rows. Throws error on the issue row.
    For Each Row In Rng.Rows
    If Row.Cells(1).Value <> "" Then
 
        Nr = Nr + 1
 
        'Collect my info here so my 
        'SQL string looks a bit nicer below.
        
        PersonID = Row.Cells(1).Value 
        'I can recommend using a Primary Key here instead.
        
        FirstName = Row.Cells(2).Value
        LastName = Row.Cells(3).Value
        Address = Row.Cells(4).Value
 
 
        SQLstringValue = 
            "(" & PersonID & ",'" & FirstName & "', '" 
            & LastName & "', '" & Address & "')"
 
        SQLstring = 
            "insert into " & MyTable & " values " & 
            SQLstringValue
        
        'Debug.Print SQLstring 
        ', great way to see if your string turns out correctly.
 
        Row.Cells(1).Activate 'Activates row for errorhandler.
        On Error GoTo ErrorCode:
            Set rs = conn.Execute(SQLstring, Records) 
            'Execute upload.
 
    End If
    Next Row
'*****************END - Inserts new data*******************

    ' Clean up, closing the serverconnection.
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
 
'Turns on screenupdating now that we are done.
Application.ScreenUpdating = True 

If Nr > 0 Then
    MsgBox ("Nice Job, Import was successful! " & Nr & " rows were imported")

Else
    MsgBox ("Please change your settings. " & Nr & " rows were imported")

End If

Exit Sub 'Stops scripts and displays which row to fix.

ErrorCode:
    MsgBox ("Incomplete upload. Please fix row: " & ActiveCell.Row & "  
    and try again")
    
End Sub

Bulk data

Now if you have a lot of rows in your excel sheet and need to optimize the upload time I would suggest the following depending on the volume:

1. Reduce the SQL uploads by uploading for example a 1000 rows in a single statement. this would require some re write of the sql statement but hopefully the above should give some guidance.

2. Use formula r1c1 in the rightest available column to to create a formula that would translate to sql code for each row. Then concatenate all rows and add starting and ending syntax for the sql part. Find more about Formula R1C1 here: https://www.pls-fix-thx.com/post/excel-vba-r1c1-absolute-reference

3. Use python or simply upload the document.

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.

SQL_Export

.zip

Download ZIP • 70KB

Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python

If you have found this article or website helpful. Please show your support by visiting the shop below.

Coffee Mug - Money Green

Buy Now