主页 / dba / 问题 / 160467
Accepted
Edmund
Asked: 2017-01-10 13:28:01 +0800 CST

将带有转换的大表复制到另一个表中

  • 772

我有一个使用导入向导从 CSV 导入的大表。我有一个从该表中选择的查询，并将所有字符串“代码”列替换为来自其他维度表的整数 ID，并对其他一些列执行一些转换。这一切都很好。

我需要将此查询的结果插入数据库中具有所需外键链接和索引的表中。这也是 SQL 对象（存储过程、函数、视图）用来访问数据的表。

我的问题是插入需要永远（1.2 小时）才能最终因抱怨事务日志的大小而死亡。我无权登录服务器。我只能通过 SSMS 访问相关数据库的 db_owner 权限。

我的问题是：

  1. 如何在不将其记录在事务日志中的情况下执行插入？我知道有BULK INSERT，OPENROWSET(BULK ...)但是这些需要一个文件。
  2. 如果我删除索引，插入会更快吗？我认为这会破坏使用目标表的现有 SQL 对象的执行计划。如果目标表上有聚簇索引，这是否可取？
  3. 记录插入不是正确的方法还是我应该使用其他方法？
sql-server sql-server-2016

2 个回答

  1. Best Answer

    如果这是一项一次性工作，您可以使用BCP Utility来完成这项任务。

    看来您的挂点正在进行大量导入，超出了您的磁盘空间资源。您需要做的是将导入分成支持的batches，BCP以避免无法放入您的服务器的事务。

    当我不得不进行一次海量数据传输时，我使用该BCP实用程序将数据提取到文件中取得了很好的成功。当然，这确实需要一个文件位置来临时保存提取的数据。

    使用MSDN 中的 BCP Utility示例作为示例。（滚动查看完整命令。）

    将数据复制到文件存储：

     bcp WorlWideImporters.Warehouse.StockItemTransactions OUT D:\BCP\StockItemTransactions_native.bcp -m 1 -n -e D:\BCP\Error_out.log -o D:\BCP\Output_out.log -S ServerA -T

    要将数据导入回数据库，BCP您应该定义批处理大小，例如-b 5000（提交事务每 5000 行）以避免填满事务日志。

    您可能还需要在您描述的大量导入期间更频繁地安排日志备份。或者您可以SIMPLE在插入数据库期间切换到模式。

    从文件存储复制到数据库

    bcp WorlWideImporters.Warehouse.StockItemTransactions_bcp IN D:\BCP\StockItemTransactions_native.bcp -b 5000 -h "TABLOCK" -m 1 -n -e D:\BCP\Error_in.log -o D:\BCP\Output_in.log -S ServerA -T

    这些是可靠的工具，但不是最奇特的。对于使用BULK INSERT导入数据，根据我的经验，它运行得更快，BCP并且它还有一个用于控制批量大小的参数。

    如果您的需求比我使用的简单示例更复杂，您应该参考BCP Utility和BULK INSERT 。

    但是，请注意这篇文章有几个答案：释放未使用的空间 SQL Server 表

    请注意，Kin 包括提到的选项 isBCP和BULK INSERT，但还有其他不错的选项是上面提到的帖子。

    • 3

  2. 是的，没有索引肯定会更快。索引将是碎片化的，无论如何都需要重建。禁用非聚集索引，然后重建它们。让集群索引保持活动状态。

    同时禁用（不检查）FK。FK 检查是很多处理。然后在大插入后将其重新打开（检查）。

    按照PK的顺序插入。

    这假设 PK 是聚集索引：

    -- drop / disable non-clusted index and FK  
select 1 
while (@@rowcount > 0)
begin 
   insert into dest (col1, col2, ...)
   select top (100000) col1, col2, ... 
   from src 
   left join dest 
   on src.PK = dest pk 
   where dest.pk is null 
   order by src.PK
end
-- rebuild / enable index and FK
    • 1

相关问题