Unanswered: How to query old data while importing new data, then switch to new, without downtime
I’m using a database for read purposes only.
The data comes in as a huge CSV feed, is imported using DTS, and is then queried until the next CSV feed arrives.
The queries themselves are quite complicated. Thousands of lines of TSQL, joins of several tables, etc. They are embedded in User Defined Functions, since views are not adequate for the required logic.
The problem: data import takes several minutes, and it is impossible to take the down the application which accesses the db for so long. I need a way to query the old data until the import is completed, and only then switch to the new data.
I should clarify that the old and new data are not mixed. The update is not added to the old data, but replaces it. The old data must be used while the new data is being input - but then the old data is simply deleted, and replaced by the newer version.
Now, CSV feeds come in often and without warning, so all this must be automated.
It has been suggested to me that I make copies of all tables, add a flag indicating which tables to use - and then write all the code twice... So...
IF (SELECT flag FROM flag_table) = 1
SELECT .... FROM table_name1
SELECT .... FROM table_name2
But this would lead to terrible code, and that after LOADS of copy-paste work..
I also thought to use sp_renamedb, importing new data to a temporary db and then taking down the actual db only for as long as is necessary to change the name of the temporary database into that of the normal one, replacing it. But this would still entail a small amount of downtime, and I’d need an exclusive lock too, which I don’t think would be easy since the application is accessing the db constantly.