Sql pull'n'push [英] Sql pull'n'push
问题描述
您好
需要一些建议。我想用另一个数据库中另一个表的数据更新表。基本上我想要某种触发器来使用sql适配器来拉动拉。或者可能每隔10秒安排一次？什么是最好的？
另一个问题，我应该使用单独的临时表吗？我的原始表使用存储过程填充临时表。我的集成然后轮询这个表并在每个记录上放置一些标记，这些记录正在被提取。也许这是最好的。我不想用更新状态等新字段修改我的原始表。
任何人做过类似的事情？
以下是我的理解..纠正我，如果我错了..
1。数据库A中有一个表X
2。您在数据库A中有一个临时表Y，它将自动填充，并且该表有一个标志列，表示获取状态
2。想要从Y中检索数据并将其更新到数据库B中的表Z
解决方案将是：
1。使用SQL适配器从表Y中提取未获取的数据。使用一个存储过程，该存储过程将仅从登台表Y
2中检索未获取的状态数据。使用数据库B中的插入存储过程生成模式
3。将从数据库A收到的消息转换为为插入生成的消息
4。使用SQL适配器将插入消息发送到数据库B
此方法优于调度，因为该过程将是仅在数据可用时初始化。如果您有任何疑问，请告诉我。
Hi
Need some advice. I want to update a table with data coming from another table in another db. Basically I want some kind of trigger to kick of a pull using the sql adapter. Or maybe schedule it every 10sec? What is best here?
Another question, should I use a separate staging table? My original table(s) populates a staging table using a stored procedure. My integration then poll this table and put some kind of flag on every record that is alreay being fetched. Perhaps that is the best. I do not want to modify my original tables with new fields for update-status etc.
Anyone done something similar?
Following is what I understood.. correct me if I am wrong..
1. You have a table X in Database A
2. You have a staging table Y in Database A which will be automatically populated and this table is having a flag column which indicates the fetched status
2. Want to retrieve data from Y and update it into table Z in Database B
Solution would be:
1. Use SQL adapter to pull unfetched data from table Y. Use a stored procedure that will retrieve only the unfetched status data from the staging table Y
2. Generate the schema using the insert stored procedure in Database B
3. Transorm the message received from Database A to the one generated for insert
4. Send the insert message to Database B using SQL adapter
This method would be better than scheduling since the process will be initialized only when data is available.. Let me know if you have any queries..
这篇关于Sql pull'n'push的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
