• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

Compare 2 tables, same structure, list changes

Experts, I have 2 tables with the same structure but not the same data.  I want to compare these 2 tables to see what is different between them.   I am importing data from excel and made a temp table before Inserting the new data.   I am comparing the temp table with the newer table of imported data.  I am not sure if there is some simple way to do this i/o writing a query comparing the 2 tables.  

I am using 2007.
0
pdvsa
Asked:
pdvsa
1 Solution
 
aikimarkCommented:
This will usually require three queries, which may be combined in a single Union query or run separately.

The first two will tell you which rows are not found in the other table, based on the key column(s). For this, you construct Left Join and Right Join queries on the key fields and check for a Null value in the 'other' table.  You only need to report the key column(s) values.  If you are constructing a union query, you would need all the fields as well as a new column, indicating the type of status ("unmatched in Temp table", "not found in production table")

The third query is an Inner Join with key column(s) equal.  I usually alias my table names to reduce the clutter. ("As T" and "As P" as an example of aliases for Temp and Production tables)  If you just wanted to identify the key columns, then you only need to have those in the Select clause.  However, you can save yourself some time by selecting all the non-key columns.
I go to the SQL view and copy the non-key columns to either Word or Excel.  In those applications, I construct the Where clause conditions.

Example:
P.DOB <> T.DOB OR
P.LastName <> T.LastName OR
P.FirstName <> T.FirstName OR
P.MiddleName <> T.MiddleName OR
P.Salary <> T.Salary

I transfer the Where clause conditions back into Access.

Note: you can also write VBA code that will construct the Where conditions string by iterating the fields of one of the tables.
0
 
pdvsaAuthor Commented:
thank you
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now