OPENROWSET (Transact-SQL)

發行項
06/06/2024

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

包含所有從 OLE DB 資料來源存取遠端資料所需的連接資訊。這個方法是存取連結伺服器資料表的另一個方法，而且是使用 OLE DB 來連接和存取遠端資料的單次特定方法。對於更常用到的 OLE DB 資料來源參考，請改用連結的伺服器。如需詳細資訊，請參閱連結的伺服器 (資料庫引擎)。函 OPENROWSET 式可以在查詢的子句中 FROM 參考，就像是數據表名稱一樣。根據 OLE DB 提供者的能力而定，OPENROWSET 函數也可以被當做 INSERT、UPDATE 或 DELETE 陳述式的目標資料表加以參考。雖然查詢可能傳回多個結果集，但是 OPENROWSET 只能傳回第一個。

OPENROWSET 也支援透過內建 BULK 提供者執行大量作業，可讓檔案資料被讀取，並且當做資料列集傳回。

本文中的許多範例僅適用於 SQL Server。其他平台上類似範例的詳細數據和連結：

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。
如需 Azure SQL 受控執行個體的範例，請參閱使用 OPENROWSET 查詢數據源。
如需 Azure Synapse 中無伺服器 SQL 集區的資訊和範例，請參閱如何在 Azure Synapse Analytics 中使用無伺服器 SQL 集區來使用 OPENROWSET。
Azure Synapse 中的專用 SQL 集區不支援函式 OPENROWSET 。

Transact-SQL 語法慣例

Syntax

OPENROWSET 語法可用來查詢外部資料來源：

OPENROWSET
(  'provider_name'
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {  [ catalog. ] [ schema. ] object | 'query' }
)

OPENROWSET(BULK) 語法是用來讀取外部檔案：

OPENROWSET( BULK 'data_file' ,
            { FORMATFILE = 'format_file_path' [ <bulk_options> ]
              | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)

<bulk_options> ::=
   [ , DATASOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_characters' ]
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]

   [ , MAXERRORS = maximum_errors ]
   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]

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

引數

一般自變數

'provider_name'

字元字串，表示登錄中所指定之 OLE DB 提供者的易記名稱（或 PROGID）。 provider_name 沒有預設值。提供者名稱範例包括 Microsoft.Jet.OLEDB.4.0、SQLNCLI 或 MSDASQL。

'datasource'

對應至特定 OLE DB 數據源的字串常數。 datasource 是 DBPROP_INIT_DATASOURCE 要傳遞至 IDBProperties 提供者介面的屬性，以初始化提供者。一般而言，此字串包含資料庫檔名、資料庫伺服器的名稱，或提供者了解尋找資料庫或資料庫的名稱。

資料來源可以是 Microsoft.Jet.OLEDB.4.0 提供者的檔案路徑 C:\SAMPLES\Northwind.mdb'，或 SQLNCLI 提供者的連接字串 Server=Seattle1;Trusted_Connection=yes;。

'user_id'

字串常數，這是傳遞至指定 OLE DB 提供者的用戶名稱。 user_id會指定連接的安全性內容，並當做 DBPROP_AUTH_USERID 屬性傳入以初始化提供者。 user_id不能是 windows 登入名稱Microsoft。

'password'

字串常數，這是要傳遞至 OLE DB 提供者的用戶密碼。 初始化提供者時，密碼 會當做 DBPROP_AUTH_PASSWORD 屬性傳入。 password 不能是Microsoft Windows 密碼。

SELECT a.* FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\SAMPLES\Northwind.mdb';
    'admin';
    'password',
    Customers
) AS a;

'provider_string'

作為屬性傳入DBPROP_INIT_PROVIDERSTRING的提供者特定連接字串，以初始化 OLE DB 提供者。 provider_string 通常會封裝將提供者初始化所需的所有連線資訊。如需 SQL Server Native Client OLE DB 提供者可辨識的關鍵詞清單，請參閱初始化和授權屬性（Native Client OLE DB Provider）。

SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    Department
) AS d;

<table_or_view>

遠端資料表或檢視表，其中包含 OPENROWSET 應該讀取的資料。可以是具有下列元件的三部分名稱物件：

catalog (選擇性) - 這是所指定物件所在的目錄或資料庫名稱。
schema (選擇性) - 這是所指定物件的結構描述或物件擁有者名稱。
object - 這是唯一識別所處理物件的物件名稱。

SELECT d.* FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    AdventureWorks2022.HumanResources.Department
) AS d;

'query'

傳送至提供者和執行的字串常數。 SQL Server 的本機實例不會處理此查詢，但會處理提供者傳回的查詢結果，這是傳遞查詢。傳遞查詢在未透過資料表名稱提供表格式數據的提供者上使用時很有用，但只能透過命令語言使用。只要查詢提供者支援 OLE DB Command 物件與其必要介面，遠端伺服器就支援通過查詢。如需詳細資訊，請參閱 SQL Server Native Client （OLE DB）介面。

SELECT a.*
FROM OPENROWSET(
    'SQLNCLI',
    'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT TOP 10 GroupName, Name FROM AdventureWorks2022.HumanResources.Department'
) AS a;

BULK 自變數

使用的數據 BULK 列集提供者 OPENROWSET ，從檔案讀取數據。在 SQL Server 中， OPENROWSET 可以從數據檔讀取數據，而不需要將數據載入目標數據表。這可讓您搭配基本SELECT語句使用OPENROWSET。

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

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

如需如何使用 BULK 選項的詳細資訊，請參閱本文稍後的<備註>一節。如需選項所需許可權 BULK 的相關信息，請參閱本文稍後的<許可權 >一節。

注意

使用完整恢復模式匯入數據時， OPENROWSET (BULK ...) 不會優化記錄。

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

BULK 'data_file'

要複製到目標數據表之數據檔的完整路徑。

SELECT * FROM OPENROWSET(
   BULK 'C:\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

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

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

BULK 錯誤處理選項

ERRORFILE = 'file_name'

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

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

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

ERRORFILE_DATA_SOURCE_NAME

從 SQL Server 2017 （14.x）開始，這個自變數是一個具名的外部數據源，指向錯誤檔案的 Azure Blob 記憶體位置，其中包含匯入期間發現的錯誤。必須使用建立 TYPE = BLOB_STORAGE外部數據源。如需詳細資訊，請參閱 CREATE EXTERNAL DATA SOURCE。

MAXERRORS = maximum_errors

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

maximum_errors 的預設值為 10。

注意

MAX_ERRORS 不適用於 CHECK 條件約束，或轉換 money 和 bigint 數據類型。

BULK 數據處理選項

FIRSTROW = first_row

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

LASTROW = last_row

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

ROWS_PER_BATCH = rows_per_batch

指定資料檔案中資料列的近似數目。這個值應該與實際的資料列數差不多。

OPENROWSET 一律將資料檔案當作單一批次加以匯入。不過，如果您為 rows_per_batch 指定 > 0 的值，查詢處理器會使用 rows_per_batch 的值作為提示，以在查詢計劃中配置資源。

ROWS_PER_BATCH預設為未知。指定 ROWS_PER_BATCH = 0 與省略 ROWS_PER_BATCH相同。

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

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

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

UNIQUE

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

如果數據檔中的實際數據列未根據指定的順序排序，或 UNIQUE 指定提示且存在重複索引鍵，則會傳回錯誤。

使用時 ORDER 需要數據行別名。數據行別名清單必須參考子句所存取 BULK 的衍生數據表。子句中指定的 ORDER 數據行名稱會參考此數據行別名清單。無法指定大型實值類型（varchar（max）、 nvarchar（max）、 varbinary（max）和 xml）和大型物件（LOB）類型（text、 ntext 和 image）數據行。

SINGLE_BLOB

將 data_file 的內容當作 varbinary(max) 類型的單一資料列、單一資料行資料列集加以傳回。

重要

我們建議您只使用 SINGLE_BLOB 選項匯入 XML 數據，而不是 SINGLE_CLOB 和 SINGLE_NCLOB，因為只 SINGLE_BLOB 支援所有 Windows 編碼轉換。

SINGLE_CLOB

以 ASCII 格式讀取 data_file，並且使用目前資料庫的定序，將內容當做 varchar(max) 類型的單一資料列、單一資料行資料列集加以傳回。

SINGLE_NCLOB

藉由以 Unicode 讀取data_file，使用目前資料庫的定序，以 nvarchar（max） 類型的單一數據列、單一數據行數據列集傳回內容。

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

BULK 輸入檔案格式選項

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

指定資料檔案中之資料的字碼頁。 CODEPAGE 只有在數據包含 字元值超過 127 或小於 32 的 char、 varchar 或 text 數據行時，才會相關。

重要

CODEPAGE Linux 上不支持的選項。

注意

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

CODEPAGE 值	描述
`ACP`	將 char、varchar 或 text 資料類型的資料行，從 ANSI/Microsoft Windows 字碼頁 (ISO 1252) 轉換成 SQL Server 字碼頁。
`OEM` (預設值)	將 char、varchar 或 text 資料類型的資料行，從系統 OEM 字碼頁轉換成 SQL Server 字碼頁。
`RAW`	不進行字碼頁之間的轉換。這是最快的選項。
`code_page`	指出在哪一個來源字碼頁，將資料檔中的字元資料加以編碼；例如 850。 SQL Server 2016 （13.x）之前的重要版本不支援代碼頁 65001（UTF-8 編碼）。

FORMAT = { 'CSV' |'PARQUET' |'DELTA' }

從 SQL Server 2017 （14.x）開始，此自變數會指定符合 RFC 4180 標準的逗號分隔值檔案。

從 SQL Server 2022 （16.x）開始，支援 Parquet 和 Delta 格式。

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

FORMATFILE = 'format_file_path'

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

您必須使用格式檔，才能定義結果集中的資料行類型。唯一的例外狀況是指定、 SINGLE_BLOB或 SINGLE_NCLOB 時SINGLE_CLOB，在此情況下，不需要格式檔案。

如需格式檔案的相關信息，請參閱使用格式檔案大容量導入資料（SQL Server）。

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

FIELDQUOTE = 'field_quote'

從 SQL Server 2017 （14.x）開始，這個自變數會指定 CSV 檔案中做為引號字元的字元。如果未指定，則會使用引號字元（"）作為 RFC 4180 標準中所定義的引號字元。

備註

唯有針對指定的提供者將 DisallowAdhocAccess 登錄選項明確設定為 0，且已啟用 [隨選分散式查詢] 進階設定選項時，才能使用 OPENROWSET 來存取 OLE DB 資料來源的遠端資料。如果未設定這些選項，預設行為不允許臨機操作存取。

當您存取遠端 OLE DB 數據源時，信任連線的登入身分識別不會從用戶端連線到所查詢伺服器的伺服器上自動委派。此時必須設定驗證委派。

如果 OLE DB 提供者支援指定之資料來源中的多個目錄和結構描述，則需要目錄和結構描述名稱。 當 OLE DB 提供者不支援這些值時，可以省略目錄和架構的值。如果提供者只支援結構描述名稱，就必須指定 schema.object 格式的兩部分名稱。如果提供者只支援目錄名稱，則必須指定 catalog.schema.object 格式的三部分名稱。您必須為使用 SQL Server Native Client OLE DB 提供者的傳遞查詢指定三部分的名稱。如需詳細資訊，請參閱 Transact-SQL 語法慣例。

OPENROWSET 不接受其自變數的變數。

對 FROM 子句中 OPENDATASOURCE、OPENQUERY 或 OPENROWSET 的任何呼叫都會與當做更新目標使用之這些函數的任何呼叫進行個別且獨立的評估，即使完全相同的引數套用至這兩種呼叫也一樣。尤其，針對其中一個呼叫結果所套用的篩選或聯結條件對於另一個呼叫的結果沒有作用。

搭配 BULK 選項使用 OPENROWSET

下列 Transact-SQL 增強功能支援函式 OPENROWSET(BULK...) ：

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

具有 BULK 選項的 OPENROWSET 在 FROM 子句中需要一個相互關聯名稱，又稱為範圍變數或別名。您可以指定資料行別名。如果未指定數據行別名清單，格式檔案必須具有數據行名稱。指定資料行別名會覆寫格式檔中的資料行名稱，例如：
- FROM OPENROWSET(BULK...) AS table_alias
- FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
重要

新增 AS <table_alias> 失敗將會導致錯誤：訊息 491，層級 16，狀態 1，行 20 必須為 FROM 子句中的大量資料列集指定相互關聯名稱。
SELECT...FROM OPENROWSET(BULK...) 陳述式會直接查詢檔案中的資料，而不將資料匯入資料表中。 SELECT...FROM OPENROWSET(BULK...) 陳述式也可以使用格式檔案來指定資料行名稱和資料類型，以列出大量資料行別名。
使用 OPENROWSET(BULK...) 作為 INSERT 或 MERGE 陳述式中的來源資料表會將資料檔案中的資料大量匯入至 SQL Server 資料表中。如需詳細資訊，請參閱使用 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時，請務必瞭解 SQL Server 如何處理模擬。如需安全性考慮的相關信息，請參閱使用 BULK INSERT 或 OPENROWSET（BULK...）將數據匯入 SQL Server。

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

OPENROWSET(BULK...)假設如果未指定，則、或 SQLBINARY 數據的最大長度SQLCHARSQLNCHAR不會超過 8,000 個字節。如果要匯入的數據位於包含任何 varchar（max）、nvarchar（max）或 varbinary（max） 對象超過 8,000 個字節的 LOB 數據欄位中，您必須使用 XML 格式檔案來定義數據欄位的最大長度。若要指定最大長度，請編輯格式檔案，並宣告 MAX_LENGTH 屬性。

注意

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

大量匯出或是匯入 SQLXML 文件

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

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

權限

OPENROWSET 權限是由傳遞給 OLE DB 提供者之使用者名稱的權限所決定。若要使用 BULK 選項，需要 ADMINISTER BULK OPERATIONS 或 ADMINISTER DATABASE BULK OPERATIONS 權限。

範例

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

A. 搭配 SELECT 和 SQL Server Native Client OLE DB 提供者使用 OPENROWSET

適用於： 僅限 SQL Server。

SQL Server Native Client (通常縮寫為 SNAC) 已從 SQL Server 2022 (16.x) 和 SQL Server Management Studio 19 (SSMS) 移除。不建議使用 SQL Server Native Client OLE DB 提供者 (SQLNCLI 或 SQLNCLI11) 和舊版 Microsoft OLE DB Provider for SQL Server (SQLOLEDB) 進行新的開發。請切換至新的 Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server 以繼續使用。

下列範例會使用 SQL Server Native Client OLE DB 提供者來存取遠端伺服器 Seattle1 上 AdventureWorks2022 資料庫中的 HumanResources.Department 資料表 (使用 SQLNCLI 和 SQL Server 會重新導向至最新版的 SQL Server Native Client OLE DB 提供者)。SELECT 陳述式可用來定義傳回的資料列集。提供者字串包含 Server 和 Trusted_Connection 關鍵字。這些關鍵字是由 SQL Server Native Client OLE DB 提供者所辨識。

SELECT a.*
FROM OPENROWSET(
    'SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
    'SELECT GroupName, Name, DepartmentID
         FROM AdventureWorks2022.HumanResources.Department
         ORDER BY GroupName, Name'
) AS a;

B. 使用 Microsoft OLE DB Provider for Jet

適用於： 僅限 SQL Server。

下列範例會透過 Microsoft OLE DB Provider for Jet，存取 Microsoft Access Northwind 資料庫中的 Customers 資料表。

注意

此範例假設已安裝 Microsoft Access。若要執行此範例，您必須安裝 Northwind 資料庫。

SELECT CustomerID, CompanyName
FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
    'admin';'',
    Customers
);

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

C. 在 INNER JOIN 中使用 OPENROWSET 和另一個數據表

適用於： 僅限 SQL Server。

下列範例會從 SQL Server Northwind 資料庫本機執行個體的 Customers 資料表中，以及從儲存在同一部電腦之 Access Northwind 資料庫的 Orders 資料表中，選取所有資料。

注意

這個範例假設您已經安裝了 Access。若要執行此範例，您必須安裝 Northwind 資料庫。

USE Northwind;
GO

SELECT c.*, o.*
FROM Northwind.dbo.Customers AS c
INNER JOIN OPENROWSET(
        'Microsoft.Jet.OLEDB.4.0',
        'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';'admin';'',
        Orders) AS o
    ON c.CustomerID = o.CustomerID;

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

D. 使用 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

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

E. 使用 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;

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

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

適用於： 僅限 SQL Server。

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

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

G. 使用格式檔案從 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;

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

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

適用於： 僅限 SQL Server。

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14.CTP1_1\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'
);

重要

ODBC 驅動程式應該是 64 位元。在 Windows 中開啟 [連接到 ODBC 數據源][SQL Server 匯入和匯出精靈] 應用程式的 [驅動程式] 索引卷標，以確認這一點。有32位 Microsoft Text Driver (*.txt, *.csv) 無法與64位版本的 sqlservr.exe搭配使用。

I. 從儲存在 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 儲存體中的數據範例。

J. 從儲存在 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 = 'YourStrongPassword1';
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;

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

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

下列範例會使用受控識別建立認證、建立外部來源，然後從裝載於外部來源的 CSV 載入資料。

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

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

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

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

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

重要

Azure SQL Database 只支援從 Azure Blob 儲存體讀取。

L. 使用 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;

M. 使用 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;

共用方式為

OPENROWSET (Transact-SQL)

Syntax

引數

一般自變數

'provider_name'

'datasource'

'user_id'

'password'

'provider_string'

<table_or_view>

'query'

BULK 自變數

BULK 'data_file'

BULK 錯誤處理選項

ERRORFILE = 'file_name'

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS = maximum_errors

BULK 數據處理選項

FIRSTROW = first_row

LASTROW = last_row

ROWS_PER_BATCH = rows_per_batch

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

UNIQUE

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

BULK 輸入檔案格式選項

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }

FORMAT = { 'CSV' |'PARQUET' |'DELTA' }

FORMATFILE = 'format_file_path'

FIELDQUOTE = 'field_quote'

備註

搭配 BULK 選項使用 OPENROWSET

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

大量匯出或是匯入 SQLXML 文件

權限

範例

A. 搭配 SELECT 和 SQL Server Native Client OLE DB 提供者使用 OPENROWSET

B. 使用 Microsoft OLE DB Provider for Jet

C. 在 INNER JOIN 中使用 OPENROWSET 和另一個數據表

D. 使用 OPENROWSET 將檔案資料大量插入 varbinary（max） 數據行

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

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

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

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

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

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

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

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

M. 使用 OPENROWSET 從 Azure Data Lake Gen2 存取數個 Delta 檔案

更多範例

相關內容

意見反應

其他資源

D. 使用 OPENROWSET 將檔案資料大量插入 varbinary（max）數據行

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

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