快速转储SQL Server表 [英] Fast dump SQL Server Table

问题描述

我在SQL Server 2008 R2中有一张大桌子.它包含数十亿行.我需要将整个数据集加载到我们的应用程序中.查询整个表非常慢.我想使用bcp将其转储到文件中并加载它.但是问题在于字符串列包含各种特殊字符，例如'\ t'，'\ 0'，逗号和'\ n'.我找不到一个好的字段/行终止符.但是长字符串终止符会减慢我的应用程序的数据文件加载速度.问题是:

I have a big table in SQL Server 2008 R2. It contains billions of rows. I need to load the whole data set in our application. Query the whole table is very slow. I want to use bcp dump it into a file and load it. But the problem is there are string columns it contains all kinds of special characters like '\t', '\0', comma, and '\n'. I can't find a good field/row terminator. But long string terminator slows down the data file loading for my application. The question is:

1. 是否有API可以比SQL查询更快地加载数据?我发现有一个本机导入API IRowsetFastLoad.但是出口并不幸运.
2. 是否有用于BCP本机格式的API?我找不到任何有关本机bcp文件格式的文档.

推荐答案

来自 -n

使用数据的本机(数据库)数据类型执行批量复制操作.此选项不会提示每个字段.它使用原始值.

Performs the bulk copy operation using the native (database) data types of the data. This option does not prompt for each field; it uses the native values.

数十亿行?然后，您还需要使用:

Billions of rows? Then you will also want to use :

-b batch_size

-b batch_size

指定每批复制的数据的行数.每一批都作为一个事务复制到服务器. SQL Server会在失败的情况下提交或回滚每个批次的事务.

Specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch.

您是否不能一次通过链接服务器访问两个数据库?这样会使事情变得更容易.

Can't you access the two databases at once, perhaps through Linked Server? It would make things easier.

DECLARE @StartId BIGINT
DECLARE @NmbrOfRecords BIGINT
DECLARE @RowCount BIGINT

SET @StartId = 0
SET @NmbrOfRecords = 9999
SET @RowCount = 1

WHILE @RowCount > 0
BEGIN
  BEGIN TRANSACTION

  INSERT INTO DestinationDatabase.dbo.Mytable
  SELECT * FROM SourceDatabase.dbo.Mytable
  WHERE ID BETWEEN @StartId AND @StartId + @NmbrOfRecords

  SET @RowCount = @@ROWCOUNT

  SET @StartId = @StartId + @NmbrOfRecords + 1

  COMMIT TRANSACTION
END

这篇关于快速转储SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！