OPENROWSET BULK （Transact-SQL）

適用於：sql Server Azure SQL 資料庫 Azure SQL 受控執行個體 Microsoft Fabric Microsoft Fabric SQL 資料庫中Microsoft網狀架構倉儲中的 SQL 分析端點

函 OPENROWSET 式會從一或多個檔案讀取資料，並將內容傳回為資料列集。 根據服務不同，檔案可能存放在 Azure Blob 儲存體、Azure Data Lake 儲存、本地磁碟、網路共享等多種方式。 你可以閱讀各種檔案格式，例如文字/CSV、Parquet 或 JSON 行。

你可以在查詢的子句中引用函 OPENROWSET 式 FROM ，就像它是資料表名稱一樣。 用它來讀取語句中的SELECT資料，或更新 、 INSERT、 CTASDELETEMERGE或 CETAS 語句中的UPDATE目標資料。

• 用 OPENROWSET(BULK) 來讀取外部資料檔案的資料。
• 使用 OPENROWSET without BULK 可以從另一個資料庫引擎讀取資料。 如需詳細資訊，請參閱 OPENROWSET （Transact-SQL）。

Tip

本文及OPENROWSET(BULK)語法在不同平台的 SQL 資料庫引擎 上有所不同。

Microsoft Fabric Data Warehouse語法請在版本下拉選單中選擇Fabric Data Warehouse。

其他平台上類似範例的詳細數據和連結：

• 如需 Azure SQL 資料庫中的詳細資訊 OPENROWSET ，請參閱使用 Azure SQL 資料庫進行資料虛擬化。
• 如需 Azure SQL 受控執行個體中的詳細資訊 OPENROWSET ，請參閱使用 Azure SQL 受控執行個體進行資料虛擬化。
• 如需 Azure Synapse 中無伺服器 SQL 集區的資訊和範例，請參閱 如何在 Azure Synapse Analytics 中使用無伺服器 SQL 集區來使用 OPENROWSET。
• Azure Synapse 中的專用 SQL 集區不支援函式 OPENROWSET 。

Transact-SQL 語法慣例

語法

針對 SQL Server、Azure SQL 資料庫、Fabric 中的 SQL 資料庫，以及 Azure SQL 管理實例：

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

有些 OPENROWSET 選項是格式專屬的，而有些則是通用的。 例如，列與欄位分隔符僅對分隔文字（CSV/TSV）有意義，而像 DATA_SOURCE 和 MAXERRORS 這類選項適用於所有格式。 下表總結了最常見格式所支援的選項。

選項	CSV（1.0）	CSV（2.0）	PARQUET	JSONL
DATA_SOURCE、ROWS_PER_BATCH、MAXERRORS	Supported	Supported	Supported	Supported
ERRORFILE、ERRORFILE_DATA_SOURCE、FORMATFILE、FORMATFILE_DATA_SOURCE	Supported	Supported	不支援	Supported
CODEPAGE，DATAFILETYPE	Supported	Supported	不支援	Supported
第一排	Supported	Supported	不支援	Supported
行終結者、田終者、田語錄、逃脫者	Supported	Supported	不支援	不支援
解析器版本	Supported	Supported	不支援	不支援
拉斯特羅	Supported	不支援	不支援	不支援
HEADER_ROW	不支援	Supported	不支援	不支援
SINGLE_BLOB，SINGLE_CLOB，SINGLE_NCLOB	不支援	不支援	不支援	不支援

Arguments

選項的 BULK 自變數可大幅控制開始和結束讀取數據的位置、如何處理錯誤，以及如何解譯數據。 例如，您可以指定將資料檔讀取為 varbinary、 varchar 或 nvarchar 類型的單列、單欄資料列集。 預設行為將在接下來的引數描述中加以描述。

如需如何使用選項 BULK 的相關資訊，請參閱本文稍後的 備註 一節。 關於該 BULK 選項所需的權限資訊，請參閱本文後面的 權限 章節。

如需準備大容量導入數據的資訊，請參閱 準備大容量匯出或匯入的數據。

BULK

讀取並回傳為一列集的資料檔案 OPENROWSET 路徑或 URI。

URI 可以參考 Azure Data Lake 記憶體或 Azure Blob 記憶體。 要讀取和傳回數據做為數據列集之數據檔的 URI。

支援的路徑格式包括：

• <drive letter>:\<file path> 存取本機磁碟上的檔案
• \\<network-share\<file path> 存取網路共用上的檔案
• adls://<container>@<storage>.dfs.core.windows.net/<file path> 以存取 Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> 存取 Azure Blob 儲存體
• s3://<ip-address>:<port>/<file path> 存取 S3 相容儲存

Note

本文和支援的 URI 模式在不同平台上有所不同。 對於Microsoft Fabric Data Warehouse中可用的 URI 模式，請在版本下拉選單中選擇Fabric Data Warehouse。

從 SQL Server 2017 （14.x） 開始， data_file 可以位於 Azure Blob 儲存體中。 如需範例，請參閱大量存取 Azure Blob 儲存體 中的數據範例。

URI 可以包含 * 該字元以匹配任意字元序列，因此 OPENROWSET 可以與 URI 進行模式比對。 此外，URI 也可以以 /** 啟用遞迴遍歷所有子資料夾。 在 SQL Server 中，從 SQL Server 2022 （16.x） 開始，此行為可供使用。

例如：

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

下表顯示 URI 可參考的儲存類型：

版本	內部部署	Azure 儲存體	Fabric 中的 OneLake	S3	谷歌雲 （GCS）
SQL Server 2017 （14.x）、SQL Server 2019 （15.x）	Yes	Yes	否	否	否
SQL Server 2022 （16.x）	Yes	Yes	否	Yes	否
Azure SQL Database	否	Yes	否	否	否
Azure SQL 受控執行個體	否	Yes	否	否	否
Azure Synapse Analytics 中的無伺服器 SQL 集區	否	Yes	Yes	否	否
Microsoft Fabric 中的 Microsoft Fabric 倉庫與 Microsoft Fabric 中的 SQL 分析端點	否	Yes	Yes	是的，使用 OneLake in Fabric的快捷鍵	是的，使用 OneLake in Fabric的快捷鍵
Microsoft Fabric 中的 SQL 資料庫	否	是的，使用 OneLake in Fabric的快捷鍵	Yes	是的，使用 OneLake in Fabric的快捷鍵	是的，使用 OneLake in Fabric的快捷鍵

DATA_SOURCE

DATA_SOURCE 定義資料檔案路徑的根位置。 它讓你能在路徑中使用 BULK 相對路徑。 建立資料來源為 CREATE EXTERNAL DATA SOURCE。

除了根位置外，它還能定義自訂憑證以存取該位置的檔案。

例如：

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

檔案格式選項

字碼頁

指定資料檔案中之資料的字碼頁。 CODEPAGE 只有在資料包含字元值大於 127 或小於 32 的 char、 varchar 或 text 直欄時才相關。 有效的值為 ACP、 OEM、 RAW，或特定代碼頁：

CODEPAGE 值	Description
ACP	將 char、 varchar 或 文字 資料類型的資料行從 ANSI/Microsoft Windows 字碼頁 （ISO 1252） 轉換為 SQL Server 字碼頁。
OEM (預設值)	將 char、 varchar 或 文字 資料類型的資料行從系統 OEM 字碼頁轉換為 SQL Server 字碼頁。
RAW	不進行字碼頁之間的轉換。 這是最快的選項。
Integer	指出在哪一個來源字碼頁，將資料檔中的字元資料加以編碼；例如 850。

Important

SQL Server 2016 （13.x） 之前的版本不支援字碼頁 65001 （UTF-8 編碼） 。 CODEPAGE Linux 上不支持的選項。

Note

除非您希望 65001 選項的優先順序高於定序/字碼頁指定值，否則建議您在格式檔案中指定每個資料行的定序名稱。

DATAFILETYPE

規定 OPENROWSET(BULK) 應讀取單位元組（ASCII、UTF8）或多位元組（UTF16）檔案內容。 有效值為 char 和 widechar：

DATAFILETYPE 數值	所有資料的表示方式如下：
char （預設）	字元格式。 如需詳細資訊，請參閱 使用字元格式匯入或匯出資料。
widechar	Unicode 字元。 如需詳細資訊，請參閱 使用 Unicode 字元格式匯入或匯出資料。

FORMAT

指定參考檔案的格式，例如：

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

有效值為 'CSV' （符合 RFC 4180 標準的逗號分隔值檔案）、'PARQUET'、'DELTA' （1.0 版） 和 'JSONL'，視版本而定：

版本	CSV	PARQUET	DELTA	JSONL
SQL Server 2017 （14.x）、SQL Server 2019 （15.x）	Yes	否	否	否
SQL Server 2022 (16.x) 及更新版本	Yes	Yes	Yes	否
Azure SQL Database	Yes	Yes	Yes	否
Azure SQL 受控執行個體	Yes	Yes	Yes	否
Azure Synapse Analytics 中的無伺服器 SQL 集區	Yes	Yes	Yes	否
Microsoft Fabric 中的 Microsoft Fabric 倉庫與 Microsoft Fabric 中的 SQL 分析端點	Yes	Yes	否	Yes
Microsoft Fabric 中的 SQL 資料庫	Yes	Yes	否	否

格式檔案

指定格式檔的完整路徑。 SQL Server 支援兩種類型的格式檔案：XML 和非 XML。

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

你需要一個格式檔來定義結果集中的欄位類型。 唯一例外是你指定 SINGLE_CLOB、 SINGLE_BLOB或 SINGLE_NCLOB;在這種情況下，你不需要格式檔。

欲了解更多格式檔案資訊，請參見使用 格式檔案批量匯入資料（SQL Server）。

從 SQL Server 2017（14.x）開始，可以format_file_path放在 Azure Blob 儲存體 裡。 如需範例，請參閱大量存取 Azure Blob 儲存體 中的數據範例。

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE 定義格式檔案路徑的根位置。 利用這個資料來源，你可以在選項中使用 FORMATFILE 相對路徑。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

建立格式 CREATE EXTERNAL DATA SOURCE檔案資料來源 。 除了根位置外，它還能定義自訂憑證以存取該位置的檔案。

文字/CSV 選項

行終結者

指定要用於 char 和 widechar 資料檔的資料列終止字元，例如：

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

預設資料列結束字元是 \r\n (新行字元)。 如需詳細資訊，請參閱 指定字段和數據列終止符。

FIELDTERMINATOR

指定要用於 char 和 widechar 資料檔的欄位終止符，例如：

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

預設欄位終止符為 , （逗號）。 如需詳細資訊，請參閱 指定欄位和列終止字元。 例如，若要從檔案讀取定位點分隔的資料：

現場引用

從 SQL Server 2017 （14.x） 開始，此引數會指定在 CSV 檔案中用作引號字元的字元，如下列紐約範例所示：

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

只能將單一字元指定為此選項的值。 如果未指定，則會使用引號字元 （"） 作為 RFC 4180 標準中定義的引號字元。 字 FIELDTERMINATOR 元 （例如逗號） 可以放在欄位引號內，而且會被視為以字元換 FIELDQUOTE 行的儲存格中的一般字元。

例如，若要讀取先前的紐約範例 CSV 資料集，請使用 FIELDQUOTE = '"'。 地址欄位的值將保留為單一值，而不是透過（引號）字元內 " 的逗號分割成多個值。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

錯誤處理選項

錯誤檔案

指定用來收集格式錯誤且無法轉換成 OLE DB 資料列集之資料列的檔案。 這些資料列會「依照原狀」，從資料檔複製到這個錯誤檔中。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

錯誤檔是在開始執行命令時建立。 如果檔案已經存在，就會引發錯誤。 此外，還會建立一個帶有副檔名 .ERROR.txt 的控制檔案。 這個檔案會參考錯誤檔中的每個資料列，且會提供錯誤診斷。 更正錯誤之後，即可載入數據。

從 SQL Server 2017 (14.x) 開始，error_file_path 可位於 Azure Blob 儲存體中。

錯誤檔案_資料來源

從 SQL Server 2017 （14.x） 開始，此引數是具名外部資料來源，指向錯誤檔案的位置，其中包含匯入期間找到的錯誤。

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

欲了解更多資訊，請參見 CREATE EXTERNAL DATA SOURCE （Transact-SQL）。

MAXERRORS

指定語法錯誤或不符合格式數據列的數目上限，如格式檔案中所定義，這可能會在擲回例外狀況之前 OPENROWSET 發生。 直到 MAXERRORS 到達為止， OPENROWSET 會忽略每個不正確的數據列，而不是載入它，並將不正確的數據列計算為一個錯誤。

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

maximum_errors的預設值為 10。

Note

MAX_ERRORS 不適用於條件約束，也不適用於 CHECK 轉換 money 和 bigint 資料類型。

資料處理選項

第一排

指定要載入之第一個資料列的號碼。 預設值為 1。 此值表示指定資料檔案的第一列。 資料列號碼是由計算資料列結束字元所決定。 FIRSTROW 是以1為基礎。

拉斯特羅

指定要載入之最後一個資料列的號碼。 預設值為 0。 此值表示指定資料檔的最後一列。

ROWS_PER_BATCH

指定資料檔案中資料列的近似數目。 此值是估計值，應該是實際列數的近似值 （在一個數量級內）。 根據預設，根據檔案特性估計 ROWS_PER_BATCH（檔案數目、檔案大小、傳回數據類型的大小）。 指定 ROWS_PER_BATCH = 0 與省略 ROWS_PER_BATCH相同。 例如：

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

ORDER （ { column [ ASC |描述 ] } [ ,... n ] [ 獨特 ] ）

選擇性提示，指定如何排序數據檔中的數據。 依預設，大量作業會假設資料檔沒有排序。 如果查詢優化器可以利用順序來產生更有效率的查詢計劃，效能可能會改善。 下列清單提供指定排序可能很有説明的範例：

• 將資料列插入具有叢集索引的資料表中，其中的資料列集資料會根據叢集索引鍵來排序。
• 將資料列集與另一個資料表聯結，其中的排序資料行和聯結資料行會相符。
• 依據排序資料行彙總資料列集資料。
• 使用數據列集做為查詢子句中的 FROM 源數據表，其中排序和聯結數據行相符。

UNIQUE

指定資料檔案沒有重複的專案。

如果資料檔中的實際列沒有依照你指定的排序順序排序，或你指定 UNIQUE 提示和重複鍵的存在，就會回傳錯誤。

使用 ORDER. 時必須使用欄位別名。 欄位別名清單必須參考子 BULK 句所存取的導出表。 你在子 ORDER 句中指定的欄位名稱指向這個欄位別名清單。 你無法指定大型值型別（varchar（max）、nvarchar（max）、varbinary（max）和xml）以及大型物件（LOB）型別（文字、ntext 和圖片）欄位。

內容選項

SINGLE_BLOB

將 data_file 的內容傳回為 varbinary（max） 類型的單列、單欄資料列集。

Important

僅SINGLE_BLOB透過選項匯入 XML 資料，而非 SINGLE_CLOB 和 SINGLE_NCLOB，因為僅SINGLE_BLOB支援所有 Windows 編碼轉換。

SINGLE_CLOB

讀取 data_file 為 ASCII，並以 varchar（max） 型別的單列單欄列集回傳內容，並利用目前資料庫的排序。

SINGLE_NCLOB

讀取 data_file 為 Unicode，並以單列單欄列列集的形式回傳內容，類型為 nvarchar（max）），並利用目前資料庫的排序。

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

Permissions

若要使用 OPENROWSET 外部資料來源，您需要以下權限：

• ADMINISTER DATABASE BULK OPERATIONS 或
• ADMINISTER BULK OPERATIONS

下列 T-SQL 範例會 ADMINISTER DATABASE BULK OPERATIONS 授與主體。

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

如果目標儲存帳號是私人帳號，你也必須在容器或儲存帳號層級將 Storage Blob 資料讀取 器角色（或更高階）分配給主體。

Remarks

• 你用的子FROM句可以呼叫OPENROWSET(BULK...)，取代資料表名稱，且功能完整SELECT。SELECT

• 具有 OPENROWSET 選項的 BULK 在 FROM 子句中需要一個相互關聯名稱，又稱為範圍變數或別名。 如果你不加入 AS <table_alias>，會收到錯誤訊息 491：「必須在 from 子句中指定一個關聯名稱來指定 bulk rowset。」

• 你可以指定欄位別名。 如果你沒有指定欄位別名清單，格式檔必須有欄位名稱。 指定欄位別名會覆蓋格式檔中的欄位名稱。 例如：

  • FROM OPENROWSET(BULK...) AS table_alias
  • FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

• SELECT...FROM OPENROWSET(BULK...) 陳述式會直接查詢檔案中的資料，而不將資料匯入資料表中。

• 語 SELECT...FROM OPENROWSET(BULK...) 句可以透過格式檔指定欄位名稱和資料型別來列出大量欄位別名。

• 透過在 or MERGE 語句中作為OPENROWSET(BULK...)來源資料表INSERT，你可以大量從資料檔案匯入資料到資料表。 欲了解更多資訊，請參見使用 BULK INSERT OPENROWSET（BULK...） 以匯入資料至 SQL Server。
• 當你在陳述INSERT句中使用這個OPENROWSET BULK選項時，子BULK句會支援表格提示。 除了一般的資料表提示 (例如 TABLOCK) 之外，BULK 子句也接受下列特殊化資料表提示：IGNORE_CONSTRAINTS (僅忽略 CHECK 和 FOREIGN KEY 限制式)、IGNORE_TRIGGERSKEEPDEFAULTS和 KEEPIDENTITY。 如需詳細資訊，請參閱資料表提示 (Transact-SQL)。
• 如需如何使用 INSERT...SELECT * FROM OPENROWSET(BULK...) 陳述式的資訊，請參閱資料的大量匯入及匯出 (SQL Server)。 關於大量匯入執行的列插入操作何時會記錄在交易日誌中的資訊，請參閱「 批量匯入中最小日誌的前置條件」。
• 當你用 OPENROWSET (BULK ...) 完整復原模型匯入資料時，它並沒有優化日誌。

Note

當您使用 OPENROWSET時，請務必瞭解 SQL Server 如何處理模擬。 有關安全考量的資訊，請參見「使用」或「OPENROWSETBULK INSERT（BULK...）」來匯入資料到 SQL Server。

大容量導入 SQLCHAR、SQLNCHAR 或 SQLBINARY 數據

OPENROWSET(BULK...)假設，如果你沒有特別指定，則 、 SQLNCHAR或 SQLBINARY 資料的最大長度SQLCHAR不會超過 8,000 位元組。 如果你匯入的 LOB 資料欄位包含任何超過 8,000 位元組的 varchar（max）、nvarchar（max） 或 varbinary（max） 物件，你必須使用 XML 格式檔案來定義該資料欄位的最大長度。 若要指定最大長度，請編輯格式檔案並宣告屬性 MAX_LENGTH 。

Note

自動產生的格式檔案不會指定 LOB 字段的長度或長度上限。 但是，您可以編輯格式檔案，並手動指定長度或最大長度。

大量匯出或是匯入 SQLXML 文件

若要大量匯出或匯入 SQLXML 資料，請在格式檔案中使用下列其中一種資料類型。

數據類型	Effect
SQLCHAR 或 SQLVARYCHAR	數據會在用戶端代碼頁或定序所隱含的代碼頁中傳送。
SQLNCHAR 或 SQLNVARCHAR	以 Unicode 格式傳送這份資料。
SQLBINARY 或 SQLVARYBIN	資料被傳送時未經任何轉換。

檔案中繼資料函式

有時候，你可能需要知道哪個檔案或資料夾來源對應結果集中的特定列。

你可以用函式 filepath 和 filename 來回傳檔案名稱和結果集的路徑。 或者你可以用它們根據檔案名稱和資料夾路徑來篩選資料。 在接下來的章節中，你會找到簡短的說明和範例。

檔名功能

此函式會回傳該列的檔案名稱。

回傳資料類型為 nvarchar（1024）。 為了最佳效能，請務必將檔名函式的結果投射到適當的資料型態。 如果你使用字元資料型別，請確保使用適當的長度。

以下範例讀取了 2017 年最後三個月的紐約市黃計程車資料檔案，並回傳每個檔案的搭乘次數。 查詢的部分 OPENROWSET 會指定要讀取哪些檔案。

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

以下範例展示了如何在子句中篩選filename()WHERE檔案以供讀取。 它會存取查詢部分的整個資料夾 OPENROWSET ，並過濾子句中的 WHERE 檔案。

你的結果和前面的例子一樣。

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

檔案路徑函數

此函式回傳完整路徑或路徑的一部分：

• 當你呼叫沒有參數的函式 filepath 時，它會回傳一個列所產生的完整檔案路徑。
• 當你呼叫 filepath 帶有參數的函式時，它會回傳路徑中與參數指定位置萬用卡相符的部分。 例如，參數值為 1 會回傳路徑中與第一個通配符符的部分。

函filepath式的回傳資料型態為 nvarchar（1024）。 為了達到最佳效能，請始終將函式的結果 filepath 投射到適當的資料型態。 如果你使用字元資料型別，請確保使用適當的長度。

以下範例為2017年最後三個月的紐約市黃計程車資料檔案。 它會回傳每個檔案路徑的騎乘次數。 查詢的部分 OPENROWSET 會指定要讀取哪些檔案。

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

以下範例展示了如何在子句中篩選filepath()WHERE檔案以供讀取。

你可以在查詢的部分使用萬用字元 OPENROWSET ，並在子句中篩選檔案 WHERE 。 你的結果會和前面的例子一樣。

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

本節提供一般範例來示範如何使用 OPENROWSET BULK 語法。

A. 使用 OPENROWSET 將 BULK INSERT 資料存檔到 varbinary（max） 欄位

適用於： 僅限 SQL Server。

下列範例會建立小型資料表以供示範之用，並將位於根目錄中Text1.txt名為C:的檔案中的檔案資料插入 varbinary（max） 資料行。

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. 使用 OPENROWSET BULK 提供者搭配格式檔案，從文本檔擷取數據列

適用於： 僅限 SQL Server。

下列範例會利用一個格式檔，從 Tab 鍵分隔的文字檔 values.txt 擷取資料列，該檔含有下列資料：

1     Data Item 1
2     Data Item 2
3     Data Item 3

格式檔 values.fmt 會描述 values.txt 中的資料行：

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

此查詢會擷取該資料：

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. 指定格式檔案和代碼頁

適用於： 僅限 SQL Server。

下列範例示範如何同時使用格式檔案和代碼頁選項。

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. 使用格式檔案從 CSV 檔案存取數據

適用於： 僅限 SQL Server 2017 （14.x） 和更新版本。

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. 從沒有格式檔案的 CSV 檔案存取數據

適用於： 僅限 SQL Server。

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

Important

ODBC 驅動程式應該是 64 位元。 在 Windows 中開啟 [連線到 ODBC 資料來源 （SQL Server 匯入和匯出精靈）] 應用程式的 [驅動程式] 索引標籤，以驗證這一點。 有 32 位元的 32 位 Microsoft Text Driver (*.txt, *.csv) 元無法與 64 位元版本 sqlservr.exe搭配。

F. 從儲存在 Azure Blob 儲存體 上的檔案存取數據

適用於： 僅限 SQL Server 2017 （14.x） 和更新版本。

在 SQL Server 2017 （14.x） 和更新版本中，下列範例會使用外部數據源，指向 Azure 記憶體帳戶中的容器，以及針對共用存取簽章建立的資料庫範圍認證。

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

如需包括設定認證和外部數據源的完整OPENROWSET範例，請參閱大量存取 Azure Blob 儲存體 中的數據範例。

G. 從儲存在 Azure Blob 儲存體 上的檔案匯入數據表

以下範例展示了如何使用OPENROWSET指令從你建立 SAS 金鑰的 Azure Blob 儲存位置的 CSV 檔案載入資料。 你可以將 Azure Blob 儲存位置設定為外部資料來源。 此過程需要一個資料庫範圍的憑證，該憑證使用共享存取簽章（SAS），並透過使用者資料庫中的主金鑰加密。

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. 針對外部來源使用受控識別

適用於： Azure SQL 受控執行個體和 Azure SQL 資料庫

以下範例是利用管理身份建立憑證，建立外部來源，然後從該外部來源的 CSV 載入資料。

首先，建立認證，並將 Blob 儲存體指定為外部來源：

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

接下來，從裝載於 Blob 儲存體的 CSV 檔案載入資料：

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. 使用 OPENROWSET 使用 S3 相容的物件記憶體來存取數個 Parquet 檔案

適用於：SQL Server 2022 (16.x) 和更新版本。

以下範例從不同位置存取多個 Parquet 檔案，這些檔案皆儲存在相容於 S3 的物件儲存中：

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. 使用 OPENROWSET 從 Azure Data Lake Gen2 存取數個 Delta 數據表

適用於：SQL Server 2022 (16.x) 和更新版本。

在此範例中，數據表容器名為 Contoso，且位於 Azure Data Lake Gen2 儲存器帳戶上。

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. 使用 OPENROWSET 查詢公用匿名數據集

下列範例使用公開可用的 NYC 黃色計程車車程記錄開啟數據集。

請先建立資料來源：

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

查詢與名稱模式相符的資料夾中副檔名的所有 .parquet 檔案：

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

其他範例

如需使用 OPENROWSET(BULK...)的更多範例，請參閱下列文章：

• 大容量導入和導出資料 （SQL Server）
• 大量匯入與匯出 XML 文件的範例 (SQL Server)
• 大量匯入資料時保留識別值 (SQL Server)
• 大量匯入時保留 null 或預設值 (SQL Server)
• 使用格式檔案大量匯入資料 (SQL Server)
• 使用字元格式匯入或匯出資料 (SQL Server)
• 使用格式檔案以略過資料表資料行 (SQL Server)
• 使用格式檔案略過資料欄位 (SQL Server)
• 使用格式檔案將資料表資料行對應至資料檔案欄位 (SQL Server)
• 在 Azure SQL 受控執行個體 中使用 OPENROWSET 查詢數據源
• 指定欄位和資料列終止符 （SQL Server）

相關內容

• DELETE （Transact-SQL）
• FROM 子句與 JOIN、APPLY、PIVOT (Transact-SQL)
• INSERT （Transact-SQL）
• 開放資料來源 （Transact-SQL）
• OPENQUERY （Transact-SQL）
• 選擇 （Transact-SQL）
• UPDATE （Transact-SQL）
• 地點 （Transact-SQL）