Record difference of two table

[fraxzi]

Hi All,

Im using two tables (either by USE command or by SQL). I need to determine the non-existing record from table1 against table2.

also, Im using ADS, is there a way to compare local table from server table ?


Anybody can help?


Thanks,
FAP

[nageswaragunupudi]

With SQL it is very easy to find rows in one table not found in another table ( on the same server ).

I have been out of touch with Advantage SQL. But I can site several constructs used for this purpose.

'MINUS' in Oracle or 'EXCEPT' in MS Sql are ( opposite of UNION ) are used to list the differences.

Code: Select all  Expand view

SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 1000
MINUS
SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 2000
ORDER BY EmpName;
 

If our SQL does not support MINUS or EXCEPT, we can always use 'NOT EXISTS' or 'NOT IN' in any SQL. Also we can use 'OUTER JOIN' and filter for NULL values.

Code: Select all  Expand view

SELECT *
FROM suppliers
WHERE not exists (select * from orders Where suppliers.supplier_id = orders.supplier_id);

or

Code: Select all  Expand view

SELECT EmpNum, EmpName
FROM EMPOLYEES
WHERE EmpNum NOT IN
(SELECT EmpNum FROM Table2)

[fraxzi]

Thanks RAO!

Yes on the same server it is easy.

but my problem is, table1 is in server and table2 is in localpc.


I cant execute SQL for two different connection.. am I right?



Regards,
FAP

[nageswaragunupudi]

I cant execute SQL for two different connection.. am I right?

Yes, unless one server has the feature to connect to other server and map it.

Have you tried opening local table and also another table on the server through the same local connection? I am not in touch with ADS now, but probably you can do that.

In any case, for small tables you can compare the two tables programatically.

[fraxzi]

..
Have you tried opening local table and also another table on the server through the same local connection? I am not in touch with ADS now, but probably you can do that..
..

Dear RAO,

we cant open table from 2 different connection unless a link is established. but I cant (or at least arc32) do that on server and local connection. I dont know if there's a solution..


Regards,
FAP

[nageswaragunupudi]

Is your requirement one way synchronization of master table on server to replicated table on local pc? If so, I suggest a different solution.

Maintain UpdateTimeStamp column in the Table.
When you want to synchronize,
<psuedo logic>
Step1: Select Max( UpdateTimeStamp ) from local table
Step2: Select * from server table where UpdateTimeStamp > #maxlocaltimestamp#
Step3: Insert new rows and update modified rows in the local table.

[fraxzi]

Is your requirement one way synchronization of master table on server to replicated table on local pc? If so, I suggest a different solution.

Maintain UpdateTimeStamp column in the Table.
When you want to synchronize,
<psuedo logic>
Step1: Select Max( UpdateTimeStamp ) from local table
Step2: Select * from server table where UpdateTimeStamp > #maxlocaltimestamp#
Step3: Insert new rows and update modified rows in the local table.

Yes. One way sync (update) local table from server.

I'll see what I can do with your great suggestions.

if 'append from' command can determine record not found from source...

like:

Code: Select all  Expand view

select( 'localdb' )  

APPEND FROM 'serverdb' where localdb->field_id <> serverdb->field_id     //... similar to this example

 

Regards,
FAP

[nageswaragunupudi]

Please try my logic. I was using this logic ( and similar logic with rowversions) for oneway sync of tables from servers of different RDMS ( example from Oracle to ADS ). This works satisfactorily