Lynn Pettis (6/21/2012)
Assuming that you are doing this update in a stored procedure, I would do something more along the lines of this psudo code:... prep work
set transaction isolation level serializable;
begin transaction
begin try
truncate destination_table;
insert into destination_table;
commit transaction
end try
begin catch
rollback transaction
... other error code as needed
end catch
end -- end of update procedure.
In conjunction with this, they could have the web application query the table using the snapshot isolation level (or set the database to read committed snapshot) so that the web app will still be able to see the data that was committed before the update transaction started.
That would eliminate even momentary blocking, and be much simple to implement than a partitioned table.
Note: You can enable the database for SNAPSHOT isolation while the database is online, but cannot be enabled for READ COMMITTED SNAPSHOT while there are users connected.