Problem with reading a database table between truncate/refresh

We need to call an external web service to get some data and store in a table locally.开发者_如何转开发 This process needs to be repeated every 10 minutes as the data that the external web service publishes changes rapidly. As part of this, we need to clear the entire table and re-insert the current data that is published by the web service.

The tricky situation we have is: What at the time the table is truncated a user queries the table and gets no results? This results to invalid result displayed to the user.

Can anyone please give me an advice on this?

Use transaction around both the operations. Something like this.

Begin transaction;
truncate table
populate the new table
end transaction

Snapshot isolation guarantees that data you will see will be consistent.

If you can create a view over your table, then you can load the data into a new table, take however long you need to to populate it, and then just alter the view to reference the new table.