CODEWITHSUNDEEP
sqlsql-servervbaexcel
Black Dagger
Black Dagger

Reputation: 399

Bulk insert into sql from an array in VBA

I am trying to build a button in excel that would upload the selected region into a table in the sql server. The first row would be automatically treated as the column headers.

How to go on about this? What I want is simplicity & super-fast uploading.

This is my idea ---

I would take the selected region and then save it as a .txt file and then run a bulk insert statement on it. Is there a method to directly bulk insert the selected region (maybe take it in a variant array first), without first saving it as a .txt file?

Also, if there is a more efficient method, then do propose.

Upvotes: 2

Views: 12868

Answers (2)

Martin
Martin

Reputation: 123

First you need to write a function (mine is called insert form).

Function insertForm(givenRange As Range) As String
--Convert each row to a string format of values
--Each value needs to have all instances of ' replaced with ''
--Each value needs to be wrapped in '
--Use Join with comma(,) to join all the values and put brackets around the whole thing
--This is the insert form for one row.
--You can get the other rows by writing the result of the function recursively
--If givenRange.Rows.Count > 1 Then
    --insertForm = insertForm & ", " & insertForm(givenRange.Offset(1, 0).Resize(givenRange.Rows.Count - 1, givenRange.Columns.Count))
--End If
End Function

So if I select 4 columns with 2 rows that contain 

1 2 3 4
5 6 7 8

This function creates ('1','2','3','4'),('5','6','7','8') I can then use this in the INSERT SQL statement as follows:

INSERT INTO TableName VALUES InsertForm(Range)

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33165

I would start with a simple ADO connection to the server and execute a bunch of INSERT INTO statements and see how it is performance-wise. If that doesn't work, then you can look at creating a text file with the same INSERT INTO statements. That may be faster, I really don't know. Here's some code to convert a range into the SQL statements you need

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 MyTable "
    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

Here's what the output would look like with some fake data I threw together:

?rangetoinsert(selection)

INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (97,100,53,27,14,53,94,43);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (21,96,69,60,70,8,35,54);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (8,12,78,38,82,67,41,53);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (15,32,89,100,61,78,16,37);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (46,37,75,66,66,93,19,45);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (82,30,56,10,99,2,1,29);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (98,39,98,96,95,60,16,73);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (65,79,69,70,74,86,15,59);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (30,37,12,57,86,94,6,53);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (1,20,91,65,20,26,96,57);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (81,7,68,65,56,27,81,80);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (94,42,43,33,46,82,44,24);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (88,48,34,83,58,64,36,90);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (51,28,43,70,12,29,96,27);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (62,54,76,86,92,41,40,84);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (11,21,32,30,65,6,22,75);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (44,72,38,73,44,93,4,16);
INSERT INTO MyTable (F1,F2,F3,F4,F5,F6,F7,F8) VALUES (39,90,96,16,9,6,17,50);

You could put this into an Execute call on your ADO connection or write it out to a text file. I can't imagine that anything else would be faster than using SQL statements. For instance, you could update the .Fields.Value properties as you loop through an updatable recordset, but there's no way that's going to be faster than this.

If you're doing a million rows, nothing in VBA will be fast, though. So keep that in mind.

Upvotes: 4

Related Questions