I need to update a Sybase ASE table from a MS Sql server (2000 now, 2005 soon).
I can get inserts to work, but how do I update ASE from MS?
The only examples I can find are trivial. Can I update with a join?
In a nutshell..
Both databases have an emp table with columns emp_id{pkey}, emp_name, emp_status
ASE linked server is ASEls, ASE server is ASE, db is myasedb
MS server is MS, db is mymsdb
insert openquery(ASEls,'select emp_id, emp_name, emp_status' from myasedb..emp where 1=0')
select emp_id, emp_name, emp_status from mymsdb
[note: there is more to the query that inserts only new rows, but it would just get in the way here]
All the examples I have found restrict the openquery select (ASEls in my case) to a single row.
I want to update all the rows that have changed since the last update.
update openquery(ASEls,'select emp_id, emp_name, emp_status' from myasedb..emp '
set emp_name = ms.dbo.emp_name,
emp_status = ms.dbo.emp_status
from ms.dbo.emp_status
where ms.dbo.emp_id = ???????????????
I'm stuck here. I do not know how the join works with openquery.
I workaround will be to create a emp_update table on ASE, insert to it, let an ASE insert trigger update the emp table, but I hoped to do it with openquery.
Thanks