Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

Identify differences between two tables

I have two tables that both come from an Excel import.   Call them yesterday's import and today's import.   The two tables have exactly the same structure as far as field names are concerned.  But I need to find a way to identify the differences between the two tables as far a field values go.  Also I need to know if a record was deleted from yesterday's table or if a record was added to today's table.  Make sense?

How can I do this?

--Steve
0
SteveL13
Asked:
SteveL13
  • 8
  • 6
  • 3
1 Solution
 
SteveL13Author Commented:
I meant to also say that if a difference is found in an existing record, what was the change.  And also if a record was removed, which record was it.  And if a record was added, which record is it.

Probably I want to end up with a report that tells me all of this.

Possible?
0
 
SimonCommented:
Something like this?

Select distinct * from
(
Select  * from yesterday
union all
select  * from today
) as subQuery
0
 
Phillip BurtonCommented:
Here's the basic code:

SELECT *
FROM (SELECT ID, 
Sum(When) AS SumOfWhen
FROM (SELECT ID, 1 AS When
FROM Table1
UNION
SELECT ID, 2
FROM Table2)  AS Mytable
GROUP BY ID)
Where SumOfWhen <> 3

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Phillip BurtonCommented:
The "SumOfWhen" will say:

1 - in the first table, but either different or deleted in the second table
2 - in the second table, but either different or deleted in the first table.
0
 
SimonCommented:
EE-28576965.accdb
This very simplistic database demonstates the principle.

Phillip's code is much better if you have unique IDs in each table and are interested in rows that are added or deleted. In it's present basic format it won't show changes to other columns in the table.
0
 
Phillip BurtonCommented:
In all of the lines which contain "ID", list all of the columns in your table. I haven't any additional data to work on.
0
 
SteveL13Author Commented:
Neither of the two tables have a primary key (ID) field.
0
 
Phillip BurtonCommented:
It will also list two rows, one with "1" and one with "2", if there are changes.
0
 
Phillip BurtonCommented:
It doesn't NEED an ID field - I don't know what fields you have, and I've got to put something.

So substitute the "ID" with the fields that you DO have.
0
 
SimonCommented:
Apologies... I can see big flaws in what I hastily suggested, and I haven't time to improve on it at present.

@Steve, if you have no primary ID field, do you have fields that can be used as a composite (multi-field) unique key?
0
 
SteveL13Author Commented:
Phillip,

I'm sorry to be difficult but I don't know how to change the SQL you provided.  Here are my fields:

EmployeeID  --  Not a Primary Key field, is a text field
Employee  -  Text field
Date of Birth  -  Date field
Position  -  Text field
Status -- Text field


--Steve
0
 
SteveL13Author Commented:
Simon.  I suppose Employee and Date Of Birth.
0
 
Phillip BurtonCommented:
No problem - SQL can be tricky:

SELECT *
FROM (SELECT EmployeeID, Employee, [Date of Birth], Position, [Status], 
Sum(When) AS SumOfWhen
FROM (SELECT EmployeeID, Employee, [Date of Birth], Position, [Status], 1 AS When
FROM Table1
UNION
SELECT EmployeeID, Employee, [Date of Birth], Position, [Status], 2
FROM Table2)  AS Mytable
GROUP BY EmployeeID, Employee, [Date of Birth], Position, [Status])
Where SumOfWhen <> 3

Open in new window

0
 
Phillip BurtonCommented:
You will need to change Table1 and Table2 to the names of your tables (and probably add [ ]s around them)
0
 
SteveL13Author Commented:
Phillip,

This worked perfectly.  Now if only it could tell me:

1) Was a record added?
2) Was a record deleted?
3) If a field data changed.
0
 
Phillip BurtonCommented:
1. If there is a row with a "1" but not a "2", then a record has been added.
2. If there is a row with a "2" but not a "1", then a record has been deleted.
3. If there are two rows, one with a "1" and a "2", then a record has been changed.
0
 
SteveL13Author Commented:
Absolutely perfect!!!  Thank you very much.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now