CODEWITHSUNDEEP
sql-servervbasql-server-2008excel
user3714330
user3714330

Reputation: 689

Copying Range from Excel to SQL without using For loop?

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?

Upvotes: 1

Views: 807

Answers (1)

Trum
Trum

Reputation: 630

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.

Upvotes: 3

Related Questions