I have the code below which works but doesn't quite do what I want it to. What I'm trying to do is write some VBA that will load whatever is in a datasheet to a teradata table. It needs to be able to handle as many rows as an xlsx can. I'm fine with the code for grabbing the data but can't figure out how to do this efficiently. I originally posted my question on an article about JDBC/Java which suggests that Teradata can take batch inserts (http://developer.teradata.com/connectivity/articles/speed-up-your-jdbcodbc-applications#comment-1366...). This has made me think that what I want to do must be possible, just that I don't know how. I don't think ADODB can handle more than one statement at a time but what are the alternatives?
/code Sub test_param_this_works()
Dim cn As New ADODB.Connection Dim cmdPrep1 As New ADODB.Command Dim prm1 As New ADODB.Parameter Dim prm2 As New ADODB.Parameter Dim strCn As String t = Timer pwd = InputBox("password?") strCn = "DSN=GDWPROD1;UID=COJNH;DATABASE=NONPERS;Password=" & pwd & "; Session Mode=ANSI;"
cn.Open strCn Set cmdPrep1.ActiveConnection = cn
cmdPrep1.CommandText = "INSERT INTO jhtest (col1,col2) VALUES (?,?)" cmdPrep1.CommandType = adCmdText cmdPrep1.Prepared = True Set prm1 = cmdPrep1.CreateParameter("param_nm1", adInteger, adParamInput, 1, 1) cmdPrep1.Parameters.Append prm1 Set prm2 = cmdPrep1.CreateParameter("param_nm2", adInteger, adParamInput, 1, 2) cmdPrep1.Parameters.Append prm2 For i = 1 To 1000 cmdPrep1("param_nm1") = i cmdPrep1("param_nm2") = i + 1 cmdPrep1.Execute Next i cn.Close MsgBox "This took: " & Timer - t
End Sub code/
If I had to absolutely do this in excel I'd script it to write the file in a delimited format and then launch tpt to load it.