Swapping ms-sql tables

Question

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.

Thanks!

Answer 1

What happens to IpToCountryOld? Do you throw it away? In which case, why not truncate IpToCountry and import my new data.

If you need to keep the data, how about storing the load date on the table and storing the "current" load date somewhere to be used in a WHERE clause? Then you switch the current date when the data is successfully loaded.

You don't say which DB you're using, so I don't know how much use this is, but do you have any stored procedures that reference the table? Be warned that on some platforms SPs are compiled using internal references to tables that will not change with a rename, so there's a risk that SPs won't pick up your new data without a recompile. The same can be true for views and stored parsed queries.

Answer 2

Can you not do the import to the one table during off hours?

Or why not just do a data update, ie update the existing records and add any new ones on a record by record basis as you loop to import the data. This would allow the table to stay live and reduce the overall impact of adding and dropping full tables.

What is the structure of the data being imported, table design, format, PK, etc? From that we may be able to give you a better answer.

Answer 3

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).

Answer 4

Hi,

Another method to implement what you are looking to achieve would be the use of table partitioning, a technique that is available in the Enterprise Edition of SQL Server.

The table name can remain the same. After your table import is complete, you just simply switch out the partition containing your old data and switch in the new partition.

The following White Paper contains all the information you would need to get started.

http://msdn.microsoft.com/en-us/library/ms345146.aspx

Cheers, John