从一个表移到表行到另一个最有效的方法 [英] Most efficient way to move table rows from one table to another

问题描述

我已经创建了一个非规范化表中需要插入/每小时更新一次。这个过程是相当复杂的，从数据上来看，所以我要寻找一个推荐的方法来更新表，而不会干扰用户。

I've created a denormalized table that needs to be inserted/updated every hour. The process is rather involved from a data perspective, so i am looking for a recommend way to update the table without disrupting the users.

我想有一个单独的表，我的过程中插入/更新，一旦完成，需要一种方法对这些变化推到我的现场制作表。

i am thinking of having a separate table that my process inserts/updates to and once complete, need a way to push those changes to my live production table.

任何帮助将是巨大的！

推荐答案

另一种解决方案是使用多个模式和播放开关-A-袋鼠。我只preFER这种方法，因为我以前做这一招的工作，有关重命名一个对象（不能是燮$ ​​P $ pssed）警告信息被灌了我的历史记录。基本上你需要另外两个模式（一个持有该表的副本暂时，和一个保存缓存副本）。

Another solution is to use multiple schemas and play switch-a-roo. I only prefer this method because I used to do this trick in a job, and the warning message about renaming an object (which can't be suppressed) was filling up my history logs. Basically you need two additional schemas (one to hold a copy of the table temporarily, and one to hold the cached copy).

CREATE SCHEMA cache AUTHORIZATION dbo;
CREATE SCHEMA hold  AUTHORIZATION dbo;

现在，在缓存模式中创建一个模拟表中的：

Now, create a mimic of the table in the cache schema:

SELECT * INTO cache.table FROM dbo.table WHERE 1 = 0;
-- then create any indexes etc.

现在，当谈到时间来刷新数据：

Now when it comes time to refresh the data:

-- step 1:
TRUNCATE TABLE cache.table;
-- (if you need to maintain FKs you may need to delete)
INSERT INTO cache.table SELECT ...

-- step 2:
-- this transaction will be almost instantaneous, 
-- since it is a metadata operation only: 

BEGIN TRANSACTION;
  ALTER SCHEMA hold  TRANSFER dbo.table;
  ALTER SCHEMA dbo   TRANSFER cache.table;
  ALTER SCHEMA cache TRANSFER hold.table;
COMMIT TRANSACTION;

从理论上讲，你可以将最后调出的交易，因为用户的可以的开始第二次转让后查询的dbo.table新的副本，但就像我说的，这几乎是瞬​​间使如果你看到在并发有什么区别我会感到惊讶。

Theoretically, you could move the last transfer out of transaction, because users could start to query the new copy of dbo.table after the second transfer, but like I said, this is almost instantaneous so I'd be surprised if you see any difference in concurrency.

您也可选择截断 cache.table 再次在这里，但我始终保持它填充，所以我可以比较的数据变化或解决，如果出事了。根据时间 - 第1步需要，它可以更快地进行转让相反，而不是从头重新填充

You could also optionally truncate cache.table again here, but I always kept it populated so I could compare data changes or troubleshoot if something went wrong. Depending on how long -- step 1 takes, it may be faster to perform the transfers in reverse than to re-populate from scratch.

重命名一样，你可以得到靠不住的东西，从这个过程中，比如他们将用实际的表统计迷路，他们不具有名称坚持。而像重命名，你要测试这一点，你可能想玩弄隔离级别，例如： RCSI的访问报告表。

Like rename, you can get wonky things from this process, such as statistics getting lost as they move with the actual table, they don't stick with the name. And like rename, you'll want to test this out and you may want to play around with isolation levels, e.g. RCSI for accessing the reporting table.

这篇关于从一个表移到表行到另一个最有效的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！