Excel VBA alternative to Fastload / batch insert


Excel VBA alternative to Fastload / batch insert


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?



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


Next i


MsgBox "This took: " & Timer - t

End Sub


Teradata Employee

Re: Excel VBA alternative to Fastload / batch insert

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.