How do I merge two tables with different column number while removing duplicates for tables with a large number of columns

Question

I have two tables Table1 and Table2 in my Access database. Table1 has 50 columns and Table2 has 30 columns. I would like to merge Table2 with Table1 without copying any entries from Table2 that have the same values as the values in column1 to column30 for an entry in Table1.

Please note that the columns order and names are the same in Table1 and Table2 up to the 30th column.

I have pretty much the same problem as this question How do I merge two tables with different column number while removing duplicates? however in his case the tables are much smaller (Table1 has 3 columns and Table2 has 2 columns), so a SQL statement would be too impractical in my case.

Is there a more practical way to accomplish this, perhaps using a combination of VBA and SQL? Thanks in advance.

Answer 1

A strictly VBA solution could look something like the following:

Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1Name", dbOpenDynaset)
Set rs1 = CurrentDb.OpenRecordset("Table2Name", dbOpenDynaset)

rs1.MoveFirst
Do While rs1.EOF <> True
  'In this example of FindFirst I am searching for a Date, String, and Number
  rs.FindFirst "Field1 = '" & rs1.Fields("Field1").value & "' AND " & _
               "Field2 = #" & rs1.Fields("Field2").value & "# AND " & _
               "Field3 = " & rs1.Fields("Field3").value

  'Check to see if the current rs record matches the current rs1 record
  'If not then the record in rs1 needs to be added to rs
  If (Not (rs.Fields("Field1").value = rs1.Fields("Field1").value And _
           rs.Fields("Field2").value = rs1.Fields("Field2").value And _
           rs.Fields("Field3").value = rs1.Fields("Field3").value)) Then
     rs1.AddNew
     rs1.Fields("Field1").value = rs1.Fields("Field1").value
     rs1.Fields("Field2").value = rs1.Fields("Field2").value
     rs1.Fields("Field3").value = rs1.Fields("Field3").value
     rs1.Fields("Field4").value = rs1.Fields("Field4").value
     rs1.Fields("Field5").value = rs1.Fields("Field5").value
     rs1.Fields("Field6").value = rs1.Fields("Field6").value
     rs1.Fields("Field7").value = rs1.Fields("Field7").value
     rs1.Update
  End If
  rs1.MoveNext
Loop

In the above FindFirst statement replace with whatever search criteria will prove that the two tables have the same record. Then the If statement must match the FindFirst. Lastly, between the rs1.AddNew and rs1.Update specify all of the fields you want to copy over.

Answer 2

I feel like the SQL MERGE statement is designed for this situation, but I can see that you may think it to be a little impractical to define each of the 30 columns which the rows are being matched on. But yeah, I would use something like:

MERGE Table1 as t1
USING (SELECT column1, column2, column3, column4 FROM Table2) as t2
ON t1.column1 = t2.column1
, t1.column2 = t2.column2
, t1.column3 = t2.column3
, t1.column4 = t2.column4
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN
INSERT(column1, column2, column3, column4)
VALUES(t2.column1, t2.column2, t2.column3, t2.column4);
GO

I don't use MERGE too often but I think that's about right. Hope it helps.

edit: this is only for SQL Server 2008 or newer.