交换 ms-sql 表 [英] Swapping ms-sql tables

问题描述

我想以最佳方式切换到表.
我有一个 IpToCountry 表，我每周根据导入的外部 CSV 文件创建一个新表.

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

这样做的问题是该表可能仍会在两者之间被访问.
我如何在 SQL 中解决这个问题?
在考虑使用 sp_getapplock 和 sp_releaseapplock，但我希望尽可能快地从表函数中读取.

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.

推荐答案

假设您无法更新/插入现有表，为什么不使用 查看?

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?

例如，您可能最初将数据存储在名为 IpToCountry20090303 的表中，您的视图将如下所示:

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

当新数据进来时，您可以创建并填充IpToCountry20090310表.填充表格后，只需更新您的视图:

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

这篇关于交换 ms-sql 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！