VBA复制和粘贴3000行 [英] VBA Copy & Paste 3000 rows

问题描述

下面的代码，是我从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行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！