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資料，或更新陳述 UPDATEINSERTDELETEMERGECTAS式中的CETAS目標資料。

• OPENROWSET(BULK) 是為從外部資料檔案讀取資料而設計的。
• OPENROWSET without BULK 是為從另一個資料庫引擎讀取而設計的。 如需詳細資訊，請參閱 OPENROWSET （Transact-SQL）。

這篇文章和 中 OPENROWSET(BULK) 設定的論點因平台而異。

• 針對 Microsoft Fabric 語法， 請在版本下拉式清單中選取 [Fabric]。
• 針對 SQL Server、Azure SQL Database 和 Azure SQL 受控執行個體語法，請 在版本下拉式清單中選取您的平台。

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

• 如需 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

Arguments

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

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

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

批量“data_file_path”

資料檔的路徑或 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]。

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

URI 可以包含 * 字元以符合任何字元序列，允許 OPENROWSET 與 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 倉儲和 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' 或 'code_page'：

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

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

FIELDQUOTE = 「field_quote'

從 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 = '"'
);

錯誤處理選項

錯誤檔案 = 'file_name'

指定用來收集格式錯誤且無法轉換成 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 = maximum_errors

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

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

maximum_errors的預設值為 10。

Note

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

資料處理選項

第一列 = first_row

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

最後一列 = last_row

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

ROWS_PER_BATCH = 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） 類型 （text、ntext 和 image） 資料行。

內容選項

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>];

如果目標記憶體帳戶是私人的，主體也必須在容器或記憶體帳戶層級指派記憶體 Blob 數據讀取者 角色（或更高層級）。

Remarks

• FROM搭配 SELECT 使用的 子句可以呼叫 OPENROWSET(BULK...) ，而不是具有完整SELECT功能的數據表名稱。

• 具有 OPENROWSET 選項的 BULK 在 FROM 子句中需要一個相互關聯名稱，又稱為範圍變數或別名。 無法在錯誤訊息 491 中新增 AS <table_alias> 結果：「必須在 from 子句中為大量資料列集指定相互關聯名稱。」

• 您可以指定資料行別名。 如果未指定數據行別名清單，格式檔案必須具有數據行名稱。 指定資料行別名會覆寫格式檔中的資料行名稱，例如：

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

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

• SELECT...FROM OPENROWSET(BULK...)陳述式可以使用格式檔案來指定資料行名稱，以及資料類型，以列出大量資料行別名。

• 在 or OPENROWSET(BULK...) 陳述式中INSERT大量用作MERGE來源表格，可將資料從資料檔案匯入資料表。 如需詳細資訊，請參閱 使用 BULK INSERT 或 OPENROWSET（BULK...） 將數據匯入 SQL Server。
• OPENROWSET BULK當 選項與語句搭配INSERT使用時，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 如何處理模擬。 如需安全性考慮的相關信息，請參閱 使用 BULK INSERT 或 OPENROWSET（BULK...） 將數據匯入 SQL Server。

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

OPENROWSET(BULK...)假設如果未指定，則 、 或 SQLCHAR 數據的最大長度SQLNCHARSQLBINARY不會超過 8,000 個字節。 如果要匯入的資料位於包含任何超過 8,000 位元組之 varchar（max）、nvarchar（max） 或 varbinary（max） 物件的 LOB 資料欄位中，您必須使用定義資料欄位長度上限的 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];

檔案路徑函數

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

• 當呼叫時未設定參數，會回傳資料列所屬的完整檔案路徑。
• 當以參數呼叫時，會回傳與參數中指定位置萬用卡相符的路徑部分。 例如，參數值 1 會回傳與第一個萬用卡相符的路徑部分。

回傳資料類型為 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 將檔案資料大量插入 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位 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 儲存體位置已設定為外部資料來源。 這需要使用在使用者資料庫中以主要金鑰加密的共用存取簽章來進行資料庫範圍認證。

-- 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）

相關內容

• 刪除 （Transact-SQL）
• FROM 子句與 JOIN、APPLY、PIVOT (Transact-SQL)
• 插入 （Transact-SQL）
• 開放資料來源 （Transact-SQL）
• OPENQUERY （Transact-SQL）
• 選擇 （Transact-SQL）
• 更新 （Transact-SQL）
• 地點 （Transact-SQL）