0

Insert Into Redshift with Excel VBA

10 Months ago I asked this question and it works, but even for 44 rows the performance is atrocious.

Redshift allows for multirow inserts and I cannot find any information on how to build a multirow insert for Redshift in excel VBA. I have tried multiple searches on the google for "excel VBA ADO Redshift multirow insert" and the like.

the row by row insert takes between 2-5 minutes depending on the machine running it and that is for only 44 lines. When we have thousands of lines it is taking hours at times.

  1. Can you do multirow inserts with ADO and VBA?
  2. If #1 is yes, can you provide examples?
  3. If #1 is no, does any one have any alternatives to improve this process?
Shenanigator
  • 1,036
  • 12
  • 45
  • https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-multi-row-inserts.html seems like what you'd need - did you try that ? If you can create SQL in that form then you should be able to execute it using ADO. – Tim Williams Mar 01 '21 at 16:34
  • I am aware that it can do that, but I don't know how to get the VBA Parameters to make multirow insert statements. I am currently looking at how to make some kind of array potentially of each value row; I have not done that before. – Shenanigator Mar 01 '21 at 16:35
  • Seems like if (eg) you have 5 fields and two rows then you'd use 10 parameters? – Tim Williams Mar 01 '21 at 16:37
  • I have 32 parameters, but those all get executed a row at a time; I don't know how to make multiple rows of 32 parameters to pass to the code. – Shenanigator Mar 01 '21 at 16:40
  • Using the example at the link above maybe something like `insert into category_stage values (default, default, default, default), (?, ?, ?, ?), (?, ?,?, ?);` where the parameters are filled by position (first to last). I'm just guessing though. – Tim Williams Mar 01 '21 at 16:43
  • The parameters are columns and row references. I don't think you can build them 1-32, 1-32,1-32..... – Shenanigator Mar 01 '21 at 16:49
  • Back to raw SQL then? – Tim Williams Mar 01 '21 at 16:50
  • Similar type of thing with SQL server: https://stackoverflow.com/questions/2479054/ado-net-batch-insert-with-over-2000-parameters – Tim Williams Mar 01 '21 at 16:52
  • Yea, I think that is the only way, but to do that I have to build an array of strings like `"VALUES(" & col1.Value"," & col2.Value & "," & col3.Value &"),"` for each row – Shenanigator Mar 01 '21 at 16:53
  • I would at least try the parameterized version first - maybe create a smaller table for testing. Another SQLserver example - see "Multiple-row parameterized INSERT statements" at https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching – Tim Williams Mar 01 '21 at 16:55
  • even with https://learn.microsoft.com/en-us/azure/azure-sql/performance-improve-use-batching I still have to build each insert statement and that might as well be row by row inserts. and for 1000 records that takes ~2 minutes – Shenanigator Mar 01 '21 at 17:01

0 Answers0