VBA - Speed Up Uploading Data to SQL from VBA

Question

I’m using a macro that writes data to a SQL Table everyday. Problem is that usually I have more than 300k rows to upload and Macro runs very slowly (more than 60 minutes). Is there anyway to speed up this process? Below part of the code, this is exactly where Macro spent most of time:

 tb = "[Table_Test]"
    Set Pos = ThisWorkbook.Sheets(“Sheet1”)
    For i = 3 To last_row

        query = "INSERT INTO " & tb & " ([DATE],[POSITION])"
        query2 = " VALUES " & "('" & Format(CDate(Pos.Cells(i, 1)), "yyyy-mm-dd") & "'" & ", '" & Pos.Cells(i, 2) & "'" &");"
        query = query & query2
        Execute_SQL_218 (query)
        query = False
        Application.StatusBar = "Uploading Data To SQL..." & i
        DoEvents
    Next i

Removing Application.StatusBar update and DoEvents could significantly trim the execution time already, but it wouldn't surprise me if bulk-inserting the records with an INSERT INTO... SELECT query, i.e. querying your worksheet through ODBC rather than generating one INSERT statement per row, would bring the whole thing down to under a minute. — Mathieu Guindon
– Mathieu Guindon, Commented Nov 27, 2018 at 21:09
Also query2 is redundant, just use a _ line continuation, no need to concatenate query & query2 — Mathieu Guindon
– Mathieu Guindon, Commented Nov 27, 2018 at 21:10

Dávid Laczkó · Accepted Answer · 2018-11-27 21:19:12Z

1

The problem is that this loop sends the data to the server row-by-row and runs a query for each. You need to send the whole thing as one recordset and insert it with one query.
As you did not provide what kind of SQL DB you have, I can only give you one suggestion for Microsoft SQL Server: first export your recordset to .csv file then instead of running the "INSERT INTO " query run a BULK INSERT.

answered Nov 27, 2018 at 21:19

Dávid Laczkó

1,0662 gold badges7 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user9585960 Over a year ago

Hi! Thanks for your answer! I'm using Microsoft SQL SERVEr, I'll try the bulk insert solution, thanks.

James Anderson Over a year ago

You can only use bulk insert if you can save the csv or text file directly on to the machine that hosts SQL server, as you can not bulk insert into SQL from a file located on a remote server location.

Collectives™ on Stack Overflow

VBA - Speed Up Uploading Data to SQL from VBA

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related