更新/删除链接服务器表上的多行 [英] Updating/Deleting multiple rows on a linked server table

问题描述

我有一个非常典型的情况，我需要将ms sql服务器中的表同步到oracle中的表.我已经成功设置了链接服务器，并且可以将新行从sql server插入到oracle表中.请注意，表中的EmployeeID和Date列在表中是唯一的，因此在特定日期仅存在一个雇员的记录.
我已经建立了一个存储过程，以检查sql server表中是否有新行，并在oracle表中执行插入操作.但是我被困在如何级联正在更新的记录上.我的意思是在ms sql服务器中，我可以选择新近更新的已经存在的行，但是我被困在将这些更新转移到oracle表中.

一种替代解决方案是从Oracle删除这些行ID，并在进行更新后从ms sql服务器重新插入它们.但是在这里，我被困在如何从作为链接服务器连接的oracle表中删除所选行的问题.

希望我很清楚.

提前谢谢大家.

Nyle

Hi,
I have a very typical situation where I need to synchronize a table in ms sql server to a table in oracle. I have successfully set up a linked server and can insert new rows from sql server to the oracle table. Note that the columns EmployeeID and Date are together unique in the table such that only one record exists for an employee on a particular date.
I have set up a stored procedure to check for new rows in sql server table and perform an insert in the oracle table. But I am stuck at how to cascade records being updated. I mean in ms sql server I can select the already existing rows which are newly updated, but I am stuck at transferring these updates to the oracle table.

An alternate solution would be to delete these row ids from the oracle and re-insert them from ms sql server after an update has been made. But here I am stuck as to how to delete selected rows from oracle table connected as a linked server.

Hope I was clear.

thanking you all in advance.

Nyle

推荐答案

找到了另一种方法:)

它实际上非常简单，但是由于可用的文档很少，整个链接服务器的现象会使您发疯.尽管如此，我使用的是openquery，这使一切变得有些复杂.删除选定的行很简单:

Found an alternate :)

Its actually very simple, but the whole linked server phenomenon can drive you crazy because of the very little documentation available. Nonetheless, I was using openquery which was making it all a bit complex. Deleting selected rows was as simple as:

delete from oracledb...mytable2
where attdate=mytable1.attdate
and empid= mytable1.empid and other conditions etc

oracledb ... mytable2是一个由四部分组成的限定符，其中oracledb是链接服务器.

欢呼.

The oracledb...mytable2 is a four part qualifier where oracledb is a linked server.

cheers.

这篇关于更新/删除链接服务器表上的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！