Need to compare two tables and list the differences

[EdLentz]

I have two identical tables in two different DBs. In the one I will be adding data, once all finished the I need will copy all that data to the other db.table. Later on I want to run a query to see if there has been any additions to the first table. Comparing the two tables seems to me the way to go. I am trying to catch someone adding data to the first table. I kinda have a query but it doesn't show the "New" additions to the first table which are not in the second table.

select id, user, description,tech
       from (
           select id, user, description, tech
           from test.devices
           union all
           select id, user, description, tech
           from test1.devices)
       temp
       group by id, user, description,tech
       having count(*)>1

This code shows all the entries in the first table, even though there is a different entry than in the second table.

Help??

Thanks

 

[feherke]

Hi

Instead of [tt]union all[/tt] you will need [tt]except[/tt].

I am trying to catch someone adding data to the first table.

That sounds more like a task for triggers instead.


Feherke.
feherke.github.io

 

[EdLentz]

Hi feherke

Thanks for replying. I briefly looked up triggers. Looks interesting. That would tell me if someone inserted into the table for sure. I would also want to know "What" they inserted. I will try the except and see if I can make that work

Thanks again

Ed

 

[EdLentz]

I figured out how to use the trigger to update a table, Works sweet. Now to send an email telling of the new record.

Thanks!