Need to synchronize a table with a query (view) in the same Sql Server Database

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 neces开发者_StackOverflow社区sary.

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?

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.