您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 OPENROWSET BULK 快速匯入文字檔案實戰

編輯：關於SqlServer

新功能：OPENROWSET BULK 提供者

SQL Server 2005 引入 OPENROWSET(BULK ...)，允許直接在 T-SQL 中以關聯式方式存取外部檔案資料，支援格式檔案描述欄位對應，效能優於傳統 BCP/xp_cmdshell。

基本語法

INSERT INTO target_table (col1, col2, ...)
SELECT col1, col2, ...
FROM OPENROWSET(
    BULK 'file_path',
    { FORMATFILE = 'fmt_file_path' | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB },
    [CODEPAGE = 'codepage'],
    [FIRSTROW = n],
    [LASTROW = n],
    [MAXERRORS = n],
    [ROWTERMINATOR = 'char'],
    [FIELDTERMINATOR = 'char']
) AS alias

實戰範例：匯入逗點分隔文字檔

來源檔 C:\DATA\ASIA.TXT：

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

目標表：

CREATE TABLE REGION (
    ID INT,
    REGION VARCHAR(25),
    DATA VARCHAR(MAX)
)

匯入指令（使用 SINGLE_CLOB 將整檔案讀為單一 CLOB 值）：

INSERT INTO REGION (ID, REGION, DATA)
SELECT 1 AS ID, 'ASIA' AS REGION, *
FROM OPENROWSET(BULK 'C:\DATA\ASIA.TXT', SINGLE_CLOB) AS MYTABLE

結果：DATA 欄位包含檔案完整文字內容。

進階：格式檔案精確對應欄位

建立 asia.fmt：

10.0
4
1       SQLCHAR       0       10      ","      1     ID               ""
2       SQLCHAR       0       50      ","      2     Name             ""
3       SQLCHAR       0       50      ","      3     City             ""
4       SQLCHAR       0       50      "\r\n"   4     Country          ""

匯入指令：

INSERT INTO REGION_DETAIL (ID, Name, City, Country)
SELECT ID, Name, City, Country
FROM OPENROWSET(
    BULK 'C:\DATA\ASIA.TXT',
    FORMATFILE = 'C:\DATA\asia.fmt'
) AS SRC

關鍵選項說明

選項	用途
SINGLE_BLOB	讀取二進位檔案為 varbinary(max)
SINGLE_CLOB	讀取文字檔案為 varchar(max)，依 CODEPAGE 解碼
SINGLE_NCLOB	讀取 Unicode 文字檔為 nvarchar(max)
FORMATFILE	指定 XML 或傳統格式檔，精確對應欄位、分隔符、結束符
FIRSTROW/LASTROW	跳過標題列、限制匯入範圍
MAXERRORS	容忍錯誤列數，預設 10