Thank you so much to the answer from jacouh (VBA Copy & Paste 3000 rows) to my initial problem. With the help of the forum, I now have the below code, which inserts 2999 rows from an Excel file. Each row is INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE. Whilst it works, it is painfully slow. As I understand, I could increase the speed of the inserts by increasing the size of the buffers, as described in http://developer.teradata.com/doc/connectivity/tdnetdp/13.11/webhelp/Teradata.Client.Provider~Teradata.Client.Provider.TdConnectionStringBuilder~ResponseBufferSize.html. I've attempted to incorporate it and failed. Could someone recommend a possible integration to increase the response buffer size. Thank you so much. I've explored different forums previously, and this is definitely the best one. Definitely. Any other ideas, better than the one I'm currently using (inserting approx 3000 rows into about 15 different tables), then please let me know!)
Sub Insert_to_TD()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim cmdsqldata As ADODB.Command
Set cmdsqldata = New ADODB.Command
Dim i, strSQL
cn.Open "DSN=NNNNNN; Username=XXXXX; Password=YYYYYYY;"
Set cmdsqldata.ActiveConnection = cn
cmdsqldata.CommandType = adCmdText
cmdsqldata.CommandTimeout = 0
For i = 1 To 2999
strSQL = ActiveSheet.Cells(i, 1).Value
cmdsqldata.CommandText = strSQL
Set rs = cmdsqldata.Execute()
Next
End Sub
Teradata can insert rows of data in parallel (like hitting F9 in Teradata SQL Assistant). You can submit a query up to 1 MB in length, with multiple statements separated by semicolons, such as:
"INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;
INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;
INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;
INSERT (X, Y, Z, ...) VALUES (X1, Y1, Z1,...) into DBNAME.TABLE;"
put that entire string with maybe 500 rows at a time into strSQL and then you only need to loop through and execute 6 statements to handle all 3000. This should speed up your process at least a factor of 10x. A non-single threaded language or some trickery with VBA will allow you to send more than one of these at a time to Teradata, which could get you an additional 10x, depending on your Teradata server's capacity and the number of connections allowed.
But Rob was on the right track with the other Teradata utilities. There is a reason those exist and it's that VBA-teradata manipulation is hard and slow.
I would suggest that you consider using a prepared statement in your VB code with parameters. Then you have a couple of options:
- Using parameters loop through the Excel worksheet a row at a time and assign the parameter values and execute iteration much like you have in your original code sample above. This will be faster than your original code.
- If possible (my ADO and VB coding skills are rusty) create an array of parameters that defines all the rows and columns in your Excel worksheet and execute a single batch statement against the database. This will be the fastest method to apply the data to the database.
I am sure there are plenty of examples on how use accomplish either method I have suggest in ADO/VB code on SO or using a Google search if you get stuck.
Alternatively, you could take the Excel worksheet and save it as a CSV that can be used as a source to one of the Teradata utilities such as BTEQ, FastLoad, MultiLoad, or Teradata Parallel Transport.
