Copying Range from Excel to SQL without using For loop?

Question

I want to copy a range (it can be one column like Set DataRange = ValidSheet.Range("C22:C81") or several column like Set DataRange = ValidSheet.Range("C22:F81")) from my Excel sheet to SQL-table.

However, some examples on internet suggests using DataRange(i) as an aray, then using For i = 1 to 59 loop to copy every value cell by cell like:

For i = 1 to 59
   SQL = "INSERT INTO Table_test (Column1) VALUES (" & DataRange(i) & ")"
Next

Is there any direct method to copy the whole range at once without using a loop?

Trum · Accepted Answer · 2015-08-28 09:23:23Z

3

I would suggest using a loop - but changing the strategy - as the INSERT statements are far more intensive and likely to slow down than Excels internal logic.

As such, I tend to create a loop which contains all of the values - one after another,for example:

For i = 1 to 59
if i = 1 then
   SQL = "INSERT INTO Table_test (Column1) VALUES (" & DataRange(i) & "),"
elseif i < 59 then
   SQL = SQL & " (" & DataRange(i) & "), "
else
   SQL = SQL & " (" & DataRange(i) & ")"
End if
Next i

   SQL.execute

This will create one big ugly string with all the values in, but would not take long - certainly for this amount of lines.

If you are doing any more than a couple of thousand however, you could create a temp text file of the range, and import using 'LOAD FROM FILE' - which is far quicker than these individual operators.

answered Aug 28, 2015 at 9:23

Trum

6303 silver badges12 bronze badges

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

4 Comments

user3714330 Over a year ago

Why is there any need for if-loop? Can't only the For-loop itself do the same job?

A ツ Over a year ago

@user3714330 you need the if because there is a first line and a last line and several in between

Trum Over a year ago

Indeed, in this case I'm building the whole picture up in one loop - so you need the first line only once, then you need to add the values each time with a comma at the end, then the last statement does not need the comma (and would cause the SQL to fall over)

user3714330 Over a year ago

Thanks for the tips, I can't test it now because of some other error, but it seems a reasonable solution

Collectives™ on Stack Overflow

Copying Range from Excel to SQL without using For loop?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related