如何使用SQL查询实时将数据从一个数据库插入另一个数据库以获取更新？ [英] How do I insert data from one DB to another in real time using SQL queries to fetch updates?

问题描述

我有一个应用程序，其数据库表需要近乎实时刷新。数据库记录源自另一个服务器上的另一个数据库，我们定期执行复杂的SQL查询以获取更新的记录。每个查询的结果集具有相同的列，这些列在应用程序DB的相应表中可用。然后将整个结果集转储/插入应用程序DB表中。我们使用Windows服务在源数据库上每15分钟执行一次这些查询，检索结果并将其插入应用程序数据库中。



我们遇到的问题是，这种结构不提供实时数据更新，并且由于其频繁运行的查询，也会给数据库服务器带来很大负担。





任何人都可以帮我们处理任何方法吗？非常感谢您的意见和建议。 :)





谢谢，

Pratik。

Hi,

I have an application whose database tables need to be refreshed in near real time. The database records originate in another database on a different server where we execute complex SQL queries periodically to fetch updated records. The result set of every query has same columns that are available in corresponding table in application DB. The entire result set is then dumped/inserted in application DB table. We use a Windows Service to execute these queries every 15 minutes on source database, retrieve results and insert them in application database.

The problem we have is that this structure does not provide real time data update and also puts a lot of load on the DB server as well due to its frequently running queries.


Could anyone help us with any approach here? Your comments and suggestions are greatly appreciated. :)


Thanks,
Pratik.

推荐答案

有可用的工具 - 查看有关复制 [ ^ ]



特别是点对点事务复制 [ ^ ]或合并复制 [ ^ ]



有还几个 [ ^ ]有关该主题的CodeProject文章

There are tools available for this - have a look at the MSDN documentation on replication[^]

Particularly peer to peer transactional replication[^] or merge replication[^]

There are also several[^] CodeProject articles on the subject

这篇关于如何使用SQL查询实时将数据从一个数据库插入另一个数据库以获取更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！