问题描述
下面的代码,是我从Yahoo Developers文章复制并相应更改的,对于Query,我想将2999行插入语句从excel复制并粘贴到Teradata.我目前的方式不会复制并粘贴整个范围.如果我将其交换为:Cells(1,1)& "&单元格(2,1)& "& Cells(3,1).... etc.直到Cells(2999)都可以.请用一种聪明,简单的方法来做到这一点?
my code below, whoch I've copied from a Yahoo Developers articles and changed accordingly, for Query, I want to copy and paste 2999 rows of insert statements from excel to Teradata. My current way doesn't copy and paste the entire range. If I swap this for: Cells(1, 1) & " " & Cells(2, 1) & " " & Cells(3, 1)....etc. until Cells(2999), it would work. A clever, simpler way of doing this please?
顺便说一句,您是否建议插入2999行的另一种方法.该表已被填充,因此FLOAD将不起作用. MLOAD还是BTEQ?我使用普通的插入语句,因为2999足够小,无法使用.但是,我总是非常感谢您提出更快的解决方案!谢谢大家!
As an aside, would you recommend an alternative method of inserting 2999 rows.The tables are already populated, so FLOAD won't work. MLOAD or BTEQ? I'm using normal insert statements because 2999 is small enough to get away with. But, I'd always be very grateful for a q quicker solution! Thank you all!
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
cn.Open "DSN=NNNNNN; Username=XXXXX; Password=YYYYYYY;"
Set cmdsqldata.ActiveConnection = cn 'This line says to which database it has to send the query
Query = Range(Cells(1, 1), Cells(2999, 1))
cmdsqldata.CommandText = Query 'We asign the query as command text
cmdsqldata.CommandType = adCmdText 'We just say what kind of command VBA has to execute
cmdsqldata.CommandTimeout = 0 'With this instruction we don't set any timeout, so the query can take all the necessary time to be executed
Set rs = cmdsqldata.Execute() 'VBA just run the query and send back the result
End Sub
推荐答案
使用VBA Join()不会导致错误:
This will cause no error, using VBA Join():
Function GetColumn1(varArray)
Dim i, i0, i1, varArrayRet
i0 = LBound(varArray, 1)
i1 = UBound(varArray, 1)
ReDim varArrayRet(i0 To i1)
For i = i0 To i1
varArrayRet(i) = varArray(i, 1)
Next
GetColumn1 = varArrayRet
End Function
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 varArray, Query
cn.Open "DSN=NNNNNN; Username=XXXXX; Password=YYYYYYY;"
Set cmdsqldata.ActiveConnection = cn 'This line says to which database it has to send the query
'
'Query = Range(Cells(1, 1), Cells(2999, 1))
'
varArray = Range("A1:A2999").Value
varArray = GetColumn1(varArray)
Query = Join(varArray, " ")
cmdsqldata.CommandText = Query 'We asign the query as command text
cmdsqldata.CommandType = adCmdText 'We just say what kind of command VBA has to execute
cmdsqldata.CommandTimeout = 0 'With this instruction we don't set any timeout, so the query can take all the necessary time to be executed
Set rs = cmdsqldata.Execute() 'VBA just run the query and send back the result
End Sub
保留:
尽管最好使用for循环,但是插入数据行/行比绑定插入的行更快,更好.
Reserve:
Although you'd better use a for loop, insert your data line/line, it will be faster and better done than a bundled insert's.
现在我们尝试逐行运行SQL吗?
Now we try run SQL line by line?
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
这篇关于VBA复制和粘贴3000行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!