stored procedure to compare data between tables

[pandapark]

hi

i have a table on server a, which holds customer details. each customer gets a unique number (numeric field) and a datetime field (sdate) when the customer was created. there is also a datetime field called edate, which if the customer moves, stamps a date and time in the edate field, and creates a new record for that customer (keeping their same unique number)
so the customers current details are select * from servera.tblCustomer where edate is null.

server b also has a table called tblCustomer which just holds one row for each customer (their most current details)
when a customer makes a call, details are added/updated to server a and a little routine (select statements within asp) runs to check if customer exists on server b, if not create them - if they already exist check the sdate - if they are different then update the customers details - that works ok.

I'd like to run a job overnight, which checks server a tblCustomer, for any changes that day (can use the sdate field for this) using the returned rows (if any) check server b - if the customer doesn't exist (can use the unique CustomerID for this) then insert, if already exist, then compare the sdate - if different then update the record in tblCustomer on server b.

i can create a simple stored procedure, which checks if exists and if not create the record, but how can i implement the comparison between sdate fields if the rcord already exists ?

thanks very much
cath

 

[sodakotahusker]

How about something like this:
just add this to the same stored procedure that you have for inserts.
You subtitute the names for your tables and fields


update t2
set t2.field1 = t1.field1.
t2.field2 = t1.field2,
t2.field3 = t1.field3
(repeat for each field you need to synchronize)

from
mytable1 t1
inner join mytable2 t2
on t1.CustomerID= t2.CustomerID
where t1.datefield <> t2.datefield

 

[pandapark]

thanks

will try that.....