Comparing 2 tables and showing the results 2

[miller1975]

Hello All,
I am attempting to do the following.
I have 2 tables, I am going to call it Tbl_Old (has old data) and Tbl_New (has new data).
Both the tables have same field names and structure. I need to compare these 2 tables and show the difference in a new table, which I will call Tbl_Result.

Any ideas on how to do this. Maybe a query to find the difference.
Also I want to know how to move data from one table to another. Again both tables have same field structure.
Please advice.

 

[PHV]

Which kind of difference ?
A starting point for missing records:
SELECT O.*, 'Tbl_Old only' AS Diff
FROM Tbl_Old O LEFT JOIN Tbl_New N ON O.PrimaryKey=N.PrimaryKey
WHERE N.PrimaryKey Is Null
UNION SELECT N.*, 'Tbl_New only'
FROM Tbl_Old O RIGHT JOIN Tbl_New N ON O.PrimaryKey=N.PrimaryKey
WHERE O.PrimaryKey Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

Hello PH,
The logic of difference is..as follows.
I will run a query and get data in Tbl_Old, then I will run the same query couple of days later and get the data this time in Tbl_New

By difference I mean, what new records are there in Tbl_New in comparison to Tbl_Old.
There are no primary keys defined at this time. Both tables are same field names.

 

[PHV]

There are no primary keys defined
Really ?
Even no unique index ?
How are your tables updatable without PK ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

Updating the tables what I do is..
Run the batch which will first empty the old table.
then move data from tbl_new to tbl_old and then empty tbl_new and i run the query dump the data..thats it..no primary key..

I hope i explained this right..
I also want to know how to move data from Tbl_New to Tbl_Old.

 

[PHV]

How do you differentiate records in tbl_new or tbl_old ?
No field without duplicate value?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

good question.
The scenario is i will run query for tbl_old today and say for example I got 100 records.
Tomorrow I will run the same query again...but this time the data is stored in tbl_new and I got say 90 or 110 records.
In comparison of Tbl_new with Tbl_old we find the records that are new unique records. These new records should be saved to the new table called Tbl_result.

Hope this helps in the explanation..let me know I can give more informaiton if requird.

 

[miller1975]

I can give you the field names if you need it also...

 

[PHV]

The real issue is which field(s) identify a record in tbl_old (and thus in tbl_new) without ambiguity.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

Is it not possible to check the entire record in one table to entire record in the other table.

 

[PHV]

check the entire record in one table to entire record in the other table
You really insist following the hard way ?
SELECT O.*, 'Tbl_Old only' AS Diff
FROM Tbl_Old O LEFT JOIN Tbl_New N
ON O.Field1=N.Field1 AND O.Field2=N.Field2 ... AND O.FieldX=N.FieldX
WHERE N.Field1 Is Null
UNION SELECT N.*, 'Tbl_New only'
FROM Tbl_Old O RIGHT JOIN Tbl_New N
ON O.Field1=N.Field1 AND O.Field2=N.Field2 ... AND O.FieldX=N.FieldX
WHERE O.Field1 Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

I am sorry but I am not trying to do it the hard way..but I cant think of any other way to get the difference between 2 tables.
can you suggest any other method...

 

[PHV]

The real issue is which field(s) identify a record in tbl_old (and thus in tbl_new) without ambiguity

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

Hello PH,
Very hard to tell which field(s) identify a record in tbl_old (and thus in tbl_new) without ambiguity as like I said both tables have almost same data except for some new ones that might have got added during the day..so basically we are trying to just find the new records that are there in Tbl_New in comparison to Tbl_Old.
This is the best explanation...

 

[miller1975]

SELECT O.*, 'Tbl_Old only' AS Diff
FROM Tbl_Old O LEFT JOIN Tbl_New N

Just curious in your query what does O and N stand for...

 

[PHV]

This is the best explanation
I disagree.
The real issue is which field(s) identify a record in tbl_old without ambiguity vs the another records in tbl_old.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[miller1975]

So if you were to do a query in a similar situation how will u do it..
I am sorry if i am not explaining things right..

 

[miller1975]

the query you have given seems to be working..but it gives one extra field called "Diff"..how can I not show up..

 

[miller1975]

I tried it and I made it as a make table query to get the difference records..and it looks good..
but the new table also shows the different records in the old table also.
the new table should be looking at the old one as master table and only the difference from new table should be shown.

 

[miller1975]

I fixed tht ....I changed the line to this so now it only show the new reocrds in Tbl_New

SELECT O.*, 'Tbl_New only' AS Diff

But it saves it as a Cross Tab query..I cant save it as a Make Table Query...help...