How do I Append Multiple Rows from one Excel Table (ListObject) to Another?

Question

I have two tables:

    Table_1         Table_2
   A   B   C       A   B   C
 -------------   -------------
1| A1| B1| C1|  1| A2| B2| C2|
2| A1| B1| C1|  2| A2| B2| C2|
3| A1| B1| C1|  3| A2| B2| C2|

Resulting table:

    Table_1   
   A   B   C  
 -------------
1| A1| B1| C1|
2| A1| B1| C1|
3| A1| B1| C1|
4| A2| B2| C2|
5| A2| B2| C2|
6| A2| B2| C2|

Table_2 is a temporary table (ListObject) that queries a database for entries using data connection.

Table_1 is a table (ListObject) that acts as a collective list of entries. It is kept separate because it (1) shortens the query time in Table_2 and (2) has some programmatic edits.

I have VBA code that copies Table_2 to Table_1 and then updates the connection string for Table_2 to not include entries with dates within the range of Table_1. The result is that Table_2 only pulls new data.

My code (correctly) copies data from Table_2 to Table_1:

For Each temprow in Table_2.ListRows
  Set newRow = table_1.ListRows.Add
  tempRow.Range.Copy
  newRow.Range.PasteSpecial xlPasteValues
Next

This works great if Table_2 (new entries) only has a dozen entries. Occasionally, Table_2 will have a couple hundred entries, which will literally take 20 minutes to complete. I suspect it's because I'm doing a couple hundred iterations of .Copy and .Paste.

Is there a way I can do it wholesale: copy ALL of Table_2 and just make it part of Table_1? I feel like what I want to do should only take 1 second to execute programmatically, not 20 minutes. I have no conditions or exceptions. I want EVERYTHING from Table_2, which should make it easy. I'm probably going about it the wrong way. Any help is appreciated. Thanks.

Answer 1

Perhaps this, changing the worksheet appropriately:

Option Explicit
Sub CombineTables()
    Dim LO1 As ListObject, LO2 As ListObject

With Sheet3
    Set LO1 = .ListObjects("Table_1")
    Set LO2 = .ListObjects("Table_2")
End With

LO2.DataBodyRange.Copy Destination:= _
    LO1.DataBodyRange.Offset(LO1.DataBodyRange.Rows.Count).Resize(1, 1)

End Sub

Answer 2

Try using the SQL union statement for fast table joining (ODBC):

SELECT * FROM [Sheet1$] UNION SELECT * FROM [Sheet2$]

where Table 1 is on a sheet named "Sheet1" and Table 2 on "Sheet2".

Do this by going to "Data->From other sources->From Microsoft Query"