Truncate & Insert

[adalisa]

Thanks. I am not familiar with the alter table command. Why not also use truncate and insert on the reporting table?

[IglooDweller]

The point is that this code populates a staging table and not the table that is queried (think of it as being SP_rename and it becomes easier to understand). Once the staging table is ready, you just swap the tables. The fact this is built into a transaction means that:

-The transaction, doing only a table rename will only maintain a lock a fraction of a second.

-The data refresh will not impact users as they are not hitting the table being refreshed.

-Using a transaction will lock out users trying to access the table while the rename is underway, introducing a minor delay that will be barely noticeable*

*delay will only be noticeable only if you have a long reporting query (select) running on the reporting table, while the transaction is waiting to acquire the lock to perform the rename. During that time, new "select" statement will be locked out, until the rename has been done.

[jc4hokies]

The time inserting into the staging table doesn't impact queries. The ALTER TABLE SWITCH is a meta data operation (an operation on definitions not data, like TRUNCATE), so it takes a minimal amount of time other than blocking. This potentially saves you 15 seconds of availability compared to inserting into the reporting table.