从表格批量插入到另一个表格

Question

我正在实现一个A/B/View场景，这意味着View指向表A，而当表B更新时，会发生切换，此时视图会指向表B，同时加载表A。

每天都会进行一次切换。有数百万行需要更新，数千个用户查看该视图。我使用的是SQL Server 2012。

我的问题是：

• 如何以最快的方式（在存储过程中）从另一个表中插入数据到一个表中？
• 是否有使用 BULK INSERT 的方法？或者，使用常规的 insert/select 是最快的方法吗？

- user1044169

Answer 1

你可以做这件事。

SELECT fieldnames
INTO DestinationTable
FROM SourceTable

正如一些回答所建议的那样，这应该是尽可能快的（取决于需要重新创建多少个索引等）。

但我建议使用同义词来改变指针从一个表到另一个表。它们非常透明，我认为比更新视图或重命名表更清晰。

- Sylvia

---

对于直接的 SELECT * 类型功能，同义词会更有效率。它们在跨数据库引用方面也非常有用。 - Philip Kelley

实际上，我可能必须坚持使用视图，因为视图提供 ALTER 功能，而同义词需要被删除和重新创建。 - user1044169

这种方法不是批量插入，会生成很多日志。 - Amirhossein Yari

Answer 2

我倾向于使用SSIS。

将表A设为OLEDB源，表B设为OLEDB目标。这样可以绕过事务日志，减轻数据库的负担。我能想到的唯一使用T-SQL实现此功能的方法是更改整个数据库的恢复模型，这远非理想，因为这意味着不仅您的传输事务而且所有事务都不会被存储。

设置SSIS传输：

创建一个新项目并将数据流任务拖到设计面板上。

双击数据流任务，进入“数据流”选项卡。然后从“数据流源”菜单中拖放一个OLE DB源，以及从“数据流目标”菜单中拖放一个OLE DB目标。

双击OLE DB源，设置连接到您的服务器，选择要从中加载的表，然后单击确定。将绿色箭头从OLE DB源拖动到目标，然后双击目标。设置连接管理器、目标表名和列映射，然后就可以开始了。 MSDN上的OLE DB源文档">OLE DB源文档 MSDN上的OLE DB目标文档">OLE DB目标文档

- GarethD

---

是的，事务日志是一个问题。你有没有关于如何将源表设置为OLEDB源的示例？网络上的示例都是关于从文本文件加载的...谢谢。 - user1044169

Answer 3

你可以使用Select ColA, ColB into DestTable_New From SrcTable语句将数据从SrcTable导入DestTable_New表中。一旦DestTable_New表被加载，重新创建索引和约束。然后将DestTable重命名为DestTable_Old，将DestTable_New重命名为DestTable。重命名非常快速。如果出现问题，你还有之前的备份表(DestTable_Old)。我曾经在一个需要每天加载数千万行数据且系统24/7运行的场景中使用过这种方法。

- Asok

Answer 4

我知道这个问题很老，但我一直在寻找答案，并没有找到真正有用的东西。是的，SSIS方法是一种可能性，但问题想要一个存储过程。

令我高兴的是，我发现了（几乎）原始问题想要的解决方案; 您可以使用CLR SP完成它。

从TableA中选择数据到DataTable，然后使用SqlBulkCopy类的WriteToServer(DataTable dt)方法，以TableB作为DestinationTableName。

唯一的轻微缺点是CLR过程必须使用外部访问才能使用SqlBulkCopy，并且不能使用上下文连接，因此您需要稍微调整权限和连接字符串。 但是！没有什么是完美的。

- user6638270

Answer 5

你可以简单地这样做

    select * into A from B Where [criteria]

根据条件从B中选择数据，并将其插入到A中，前提是列相同或者您可以指定列名而不是使用*。

- Furqan Hameedi

---

他要求使用“批量”而不是常规加载。这个答案的问题在于对于大型传输，它会使您的事务日志爆炸。使用where循环、CLR、SSIS或BULK INSERT进行分批处理是首选方法，有时也是唯一的解决方案。 - Ali Razeghi - AWS

@AliRazeghi，请再仔细阅读一下问题，用户询问的是将数据从一个表格快速插入到另一个表格的最快方法，而不考虑简单SQL、批量插入、事务日志可维护性等问题。 - Furqan Hameedi

我不是给你的答案点踩的人，但在生产系统中有数百万行数据时，根据隔离级别和硬件限制，可能会导致很多锁定/阻塞问题。这可能需要很长时间才能解决。使用"select * into a from b"语句可以实现最简单的结果，但速度较慢。 - Ali Razeghi - AWS

Answer 6

INSERT... SELECT...函数与BULK INSERT的功能非常相似。像@GarethD所说的那样，你可以使用SSIS，但是如果你只是从table1复制行到table2，那可能会过于复杂。

如果你要复制大量的数据，请注意事务日志——在进行大型插入操作时，它可能会迅速增长。一个解决方法是通过循环执行插入语句来对要插入的数据进行分块处理，比如每次处理100,000或10,000行（取决于你的行有多宽，即每次传输多少MB）。

（只是好奇，你是用ALTER VIEW重置视图吗？我曾经做过类似的事情，不过我们需要四个表和四个视图来支持过去/现在/未来/交换集。）

- Philip Kelley

---

是的，我目前正在执行 ALTER VIEW 操作，但我想根据 Sylvia 的建议改用同义词。 - user1044169

我撤回之前的说法——视图似乎是更好的选择，因为它们提供了 ALTER 功能，而同义词仅限于删除/创建。 - user1044169

我来晚了，但是......如果你在一个事务中删除和创建同义词，它将呈现为原子操作。 - Graham