主页 / dba / 问题 / 262605
Accepted
axdna
Asked: 2020-03-25 06:57:07 +0800 CST

为什么 BULK INSERT 以随机顺序插入数据？

  • 772

我有一个csv包含 350,000 行的文件。想以与csv文件中相同的顺序将数据插入临时表。我正在尝试BULK INSERT使用：

BULK INSERT ##AllRows
    FROM @FilePath
    WITH
    (
    FIELDTERMINATOR = '\n'
    )

不幸的是BULK INSERT，以随机顺序插入数据。我每次执行的标题都在不同的行中。我在 SQL Server 2016 上运行它。是否有可能在旧版本的 SQL Server 中顺序方式不同？

使用该FIRSTROW选项不会将标题识别为文件的第一行。文件没有我们可以订购的任何列。在文件中，标题总是在第一行。

这可能是巧合，但即使FIRSTROW=2我的标题有可能出现在表格中。我检查了它。看起来csv文件中包含的行越多，插入到表中的可能性就越大。

sql-server csv

2 个回答

  1. Best Answer

    文件中的行按顺序读取，并按相同顺序添加到表中。

    当您从表中读取行时会出现此问题。如果您的 没有ORDER BY子句SELECT，SQL Server 可以自由地以任何方便的顺序从表中返回行。

    细节

    该问题没有为 table 提供定义##AllRows，但似乎可以确定该表是一个堆（没有聚集索引的表）。SQL Server 使用索引分配映射 (IAM) 结构从堆中读取页面。这意味着数据倾向于在每个 IAM 链中按文件和页面 ID 顺序返回，这通常不会反映插入数据的顺序。这是您所看到的行为的根本原因。

    解决方案

    您需要一列来指示文件中行的顺序，然后在编写查询时按该列排序。不幸的是，SQL Server 没有提供在导入期间添加此“序列”列的内置方法。

    有几个常见的解决方法：

    1. 在 SQL Server 外部预处理源文件，为每一行添加一个序列号。这是最可靠的方法。
    2. 在导入期间分配序列号。

    第二种方法有一定的风险，因为没有书面保证可以保证在所有情况下都能可靠地工作。尽管如此，人们已经成功地使用了这个想法很长时间了。总体思路是：

    • 向导入表添加一IDENTITY列。
    • 在导入表上创建一个视图，省略IDENTITY列。
    • BULK INSERT进入视野。

    这不适用于全局临时表，因为视图无法引用该类型的表。您需要改用常规表（可能在tempdb中）。

    示例 1

    我使用以下脚本成功导入了一个包含莎士比亚全集的 csv 文件：

    第一步是在tempdb中创建一个带有额外IDENTITY列的表：

    USE tempdb;
GO
CREATE TABLE dbo.Test
(
    id integer IDENTITY PRIMARY KEY,
    line nvarchar(4000) NOT NULL
);

    现在我们在该表上创建一个视图，省略IDENTITY列：

    CREATE VIEW dbo.ImportTest
WITH SCHEMABINDING
AS
SELECT
    T.line
FROM dbo.Test AS T;

    最后，我们批量插入到视图中：

    BULK INSERT dbo.ImportTest
FROM 'C:\Temp\shakespeare.txt'
WITH 
(
    CODEPAGE = '65001',
    DATAFILETYPE = 'char',
    ROWTERMINATOR = '\n'
);

    我们现在可以使用SELECTwith来查看文件顺序的前几行ORDER BY：

    SELECT TOP (20)
    T.id,
    T.line
FROM dbo.Test AS T 
ORDER BY
    T.id ASC;

    结果以正确的顺序显示文本：

    查询结果

    示例 2

    也可以OPENROWSET与格式文件一起使用。使用相同的示例 csv 文件，我能够使用以下格式文件（另存为shakespeare.xml）导入数据：

    <?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\r\n"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="line" xsi:type="SQLNVARCHAR" NULLABLE="NO"/>
 </ROW>
</BCPFORMAT>

    和：

    INSERT dbo.Test
    WITH (TABLOCK)
    (
        line
    )
SELECT
    ORO.line
FROM OPENROWSET
(
    BULK 'C:\Temp\shakespeare.txt',
    FORMATFILE = 'C:\Temp\shakespeare.xml',
    CODEPAGE = '65001'
) AS ORO;

    请注意，此方法不需要视图，因此您可以针对全局临时表。目标表仍然需要额外的IDENTITY列。

    • 4

  2. 通过使用参数引导您的 csv 批量插入...

    https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver15

    具体来说 ：

    FIRSTROW = first_row -> 从文件导入的第一行（从 1 开始计数）

    KEEPIDENTITY -> 指定导入数据文件中的一个或多个标识值将用于标识列。如果未指定 KEEPIDENTITY，则验证此列的标识值但不导入，并且 SQL Server 会根据在表创建期间指定的种子值和增量值自动分配唯一值。如果数据文件不包含表或视图中标识列的值，则使用格式文件指定导入数据时要跳过表或视图中的标识列；SQL Server 自动为列分配唯一值。有关详细信息，请参阅 DBCC CHECKIDENT (Transact-SQL)。

    ORDER ( { column [ ASC | DESC ] } [ ,... n ] ) -> 指定数据文件中的数据如何排序。如果要导入的数据根据​​表上的聚集索引（如果有）进行排序，则可以提高批量导入性能。如果数据文件以不同的顺序排序，即不是聚集索引键的顺序，或者如果表上没有聚集索引，则忽略 ORDER 子句。提供的列名必须是目标表中的有效列名。默认情况下，批量插入操作假定数据文件是无序的。对于优化的批量导入，SQL Server 还会验证导入的数据是否已排序。

    n 是一个占位符，表示可以指定多个列。

    编辑：很抱歉读到它并没有完全帮助你 - 但我在所述页面的末尾附近发现了这个：

    从 CSV 文件导入数据 从 SQL Server 2017 (14.x) CTP 1.1 开始，BULK INSERT 支持 CSV 格式，Azure SQL 数据库也是如此。在 SQL Server 2017 (14.x) CTP 1.1 之前，SQL Server 批量导入操作不支持逗号分隔值 (CSV) 文件。但是，在某些情况下，可以将 CSV 文件用作将数据批量导入 SQL Server 的数据文件。有关从 CSV 数据文件导入数据的要求的信息，请参阅为批量导出或导入准备数据 (SQL Server)。

    • 0

相关问题