需要同步同一个Sql Server数据库中的表和查询（视图）
我需要使表与连接到多个表的递归 CTE 查询保持同步。 CTE 每次运行时可能会返回不同的结果（如果对其连接的表进行了任何更改）。 CTE 很慢，我无法让触发器运行它，因为导致触发器触发的查询直到触发器触发后才会返回（我不希望对单个记录进行简单更新） 15 秒，因为它正在等待触发器完成）。有关如何在 SSMS 中实施解决方案的任何详细信息也很好，但不是必需的。
一种解决方案是让计划作业运行 CTE，并将其结果与表合并。计划的作业可以每隔几秒运行一次，以便表尽可能保持最新。但我不知道如何在 Sql Server 2005 中创建计划作业，也不知道如何将 CTE 查询的结果与表合并。
重申我的问题：
如何使用 CTE 使表尽可能接近实时地保持最新状态？
I need to keep a table in sync with a recursive CTE query that joins to multiple tables. The CTE may return different results every time it's ran (if any changes were made to the tables it joins on). The CTE is slow and I can't afford to have a trigger run it since the query that caused the trigger to fire won't return until after the trigger has fired (I don't want a simple update to a single record to take 15 seconds because it's waiting on the trigger to finish). Any details on how to implement the solution in SSMS would be nice too, but not necessary.
One solution would be to have a scheduled job run the CTE, and merge it's results with the table. The scheduled job can run every few seconds so the table is as up-to-date as possible. But I don't know how to create a scheduled job in Sql Server 2005 or how to Merge the results of a CTE query with a table.
To restate my question:
How do I keep the table up to date as close to real-time as possible using the CTE?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我自己想出来了。使用 Sql Server Agent 以及非常酷的 tablediff.exe 实用程序，我能够使用 tablediff 实用程序生成的 upsert/delete 语句保持视图和表同步。它需要一个临时表和视图中的一个唯一列（我通过将 2 个列 ID 连接成一个列 ID 创建该列（我知道这会使其独一无二））。
这是一个更好的解决方案，因为我不必截断活动表或删除它并重命名临时表。另外，它在自己的线程/环境中运行，因此如果运行需要一分钟也没关系（与使用始终需要快速的触发器相比）。
如果有人想要更多详细信息，请告诉我，我将更新这篇文章。
I figured it out myself. Using the Sql Server Agent, along with the very cool tablediff.exe utility, I was able to keep the view and table in sync using the upsert/delete statements generated by the tablediff util. It required a temp table and a unique column in the view (which I created by concatenating 2 column ids into one (which I knew would make it unique)).
This was a much better solution because I didn't have to truncate the live table or drop it and rename the temp table. Plus it runs in it's own thread/environment so it's ok if it takes a minute to run (vs using a trigger which always needs to be fast).
If anyone wants more details let me know and I'll update this article.