从表到表批量插入 [英] Bulk Insert from table to table

问题描述

我正在实现 A / B / View 方案，这意味着 View 指向表A ，而表B 已更新，然后发生切换，并且在加载表A时视图指向表B。

每天进行一次切换。有数百万行要更新，成千上万的用户正在查看该视图。我正在使用SQL Server 2012。

我的问题是：

• 如何做我以最快的方式将数据从另一个表插入到表中吗？ （在存储的proc内）


• 是否可以使用BULK INSERT？还是使用常规插入/选择最快的方式？

解决方案

 选择字段名
 INTO DestinationTable 
 FROM SourceTable 
  

几个答案表明，这应该尽可能快（取决于您需要重新创建多少索引，等等）。 / p>

但是我建议使用 同义词 ，以便将指针从一个表更改为另一个表。它们非常透明，在我看来，比更新视图或重命名表还干净。

I am implementing an A/B/View scenario, meaning that the View points to table A, while table B is updated, then a switch occurs and the view points to table B while table A is loaded.

The switch occurs daily. There are millions of rows to update and thousands of users looking at the view. I am on SQL Server 2012.

My questions are:

• how do I insert data into a table from another table in the fastest possible way? (within a stored proc)
• Is there any way to use BULK INSERT? Or, is using regular insert/select the fastest way to go?

解决方案

You could do the

SELECT fieldnames
INTO DestinationTable
FROM SourceTable

as a couple answers suggest, that should be as fast as it can get (depending on how many indexes you'd need to recreate, etc).

But I would suggest using synonyms in order to change the pointer from one table to another. They're very transparent and in my opinion, cleaner than updating the view, or renaming tables.

这篇关于从表到表批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！