I want to swap to tables in the best possible manner.
I have an IpToCountry table, and I create a new one on a weekly basis according to an external CSV file which I import.
The fastest way I've found to make the switch was doing the following:
sp_rename IpToCountry IpToCountryOld
go
sp_rename IpToCountryNew IpToCountry
go
The problem with this is that the table might still be accessed in between.
How do I approach this problem in SQL?
In considered using sp_getapplock and sp_releaseapplock, but I want to keep the read from the table function as quick as possible.
Answer
Assuming that you're unable to update/insert into the existing table, why don't you wrap all access to the table using a view?
For example, you might initially store your data in a table called IpToCountry20090303, and your view would be something like this:
CREATE VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090303
When the new data comes in, you can create and populate the IpToCountry20090310 table. Once the table is populated just update your view:
ALTER VIEW IpToCountry
AS
SELECT * FROM IpToCountry20090310
The switch will be completely atomic, without requiring any explicit locking or transactions. Once the view has been updated, you can simply drop the old table (or keep it if you prefer).