為什麼 BULK INSERT 以隨機順序插入數據？

我有一個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文件中包含的行越多，插入到表中的可能性就越大。

文件中的行按順序讀取，並按相同順序添加到表中。

當您從表中讀取行時會出現此問題。如果您的 沒有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列。

引用自：https://dba.stackexchange.com/questions/262605