OPENROWSET (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Содержит все необходимые сведения о соединении, которые требуются для доступа к удаленным данным источника данных OLE DB. Это альтернативный метод для доступа к таблицам на связанном сервере и является однократным нерегламентированным методом соединения и удаленного доступа к данным с помощью OLE DB. Вместо этого для более частых ссылок на источники данных OLE DB используйте связанные серверы. Дополнительные сведения см. в статье Связанные серверы (ядро СУБД). На OPENROWSET функцию можно ссылаться в FROM предложении запроса, как если бы это было имя таблицы. Функция OPENROWSET также может использоваться как целевая таблица в инструкции INSERT, UPDATE или DELETE. Это зависит от возможностей поставщика OLE DB. Несмотря на то, что запрос может возвратить несколько результирующих наборов, функция OPENROWSET возвращает только первый из них.

OPENROWSET также поддерживает массовые операции с помощью встроенного поставщика BULK, позволяющего считывать данные из файла и возвращать их в виде набора строк.

Многие примеры в этой статье применяются только к SQL Server. Подробные сведения и ссылки на аналогичные примеры на других платформах:

• База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.
• Примеры Управляемый экземпляр SQL Azure см. в статье "Запрос источников данных" с помощью OPENROWSET.
• Сведения и примеры с бессерверными пулами SQL в Azure Synapse см. в статье "Использование OPENROWSET с использованием бессерверного пула SQL в Azure Synapse Analytics".
• Выделенные пулы SQL в Azure Synapse не поддерживают функцию OPENROWSET .

Соглашения о синтаксисе Transact-SQL

Синтаксис

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. источник данных — это DBPROP_INIT_DATASOURCE свойство, передаваемое IDBProperties в интерфейс поставщика для инициализации поставщика. Как правило, эта строка содержит имя файла базы данных, имя сервера базы данных или имя, которое поставщик понимает для поиска базы данных или баз данных.

Источником данных может быть путь к файлу C:\SAMPLES\Northwind.mdb' для поставщика Microsoft.Jet.OLEDB.4.0 или строка подключения Server=Seattle1;Trusted_Connection=yes; для поставщика SQLNCLI.

'user_id'

Строка константы, которая является именем пользователя, переданного указанному поставщику OLE DB. user_id указывает контекст безопасности подключения и передается в качестве DBPROP_AUTH_USERID свойства для инициализации поставщика. user_id не может быть именем входа в Microsoft Windows.

'password'

Строковая константа, которая является паролем пользователя, передаваемой поставщику OLE DB. пароль передается в качестве DBPROP_AUTH_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 обычно инкапсулирует все необходимые сведения о подключении для инициализации поставщика. Список ключевых слов, распознаемых поставщиком OLE DB собственного клиента SQL Server, см. в разделе "Свойства инициализации" и "Авторизация" (поставщик OLE DB собственного клиента).

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

<table_or_view>

Удаленная таблица или представление, содержащие данные, которые должны быть прочитаны OPENROWSET. Это может быть объект с именем из трех частей со следующими компонентами:

• каталог (необязательно) — имя каталога или базы данных, в которых хранится указанный объект.
• схема (необязательно) — имя схемы или владелец указанного объекта.
• объект — имя объекта, уникальным образом идентифицирующее объект, с которым производится взаимодействие.

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 можно считывать из файла данных без загрузки данных в целевую таблицу. Это позволяет использовать OPENROWSET с базовой SELECT инструкцией.

Аргументы BULK параметра позволяют значительно контролировать, где начать и завершить чтение данных, как справиться с ошибками и способом интерпретации данных. Например, можно указать, что файл данных считывается в виде однострочного набора строк типа varbinary, varchar или nvarchar. Поведение по умолчанию описано в следующем далее описании аргументов.

Дополнительные сведения об использовании параметра см. в разделе "ПримечанияBULK" далее в этой статье. Сведения о необходимых разрешениях 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 может находиться в Хранилище BLOB-объектов Azure. Примеры массового доступа к данным см. в Хранилище BLOB-объектов Azure.

Параметры обработки ошибок BULK

ERRORFILE = "file_name"

Указывает файл, используемый для сбора строк, содержащих ошибки форматирования, которые не могут быть преобразованы в набор строк OLE DB. Эти строки без изменений копируются из файла данных в файл ошибок.

Файл ошибок создается в начале выполнения команды. Ошибка возникает, если файл уже существует. Дополнительно создается управляющий файл с расширением ERROR.txt. Этот файл ссылается на каждую строку в файле ошибок и позволяет провести их диагностику. После исправления ошибок данные можно загрузить.

Начиная с SQL Server 2017 (14.x), error_file_path можно использовать Хранилище BLOB-объектов Azure.

ERRORFILE_DATA_SOURCE_NAME

Начиная с SQL Server 2017 (14.x), этот аргумент является именованным внешним источником данных, указывающим на расположение хранилища BLOB-объектов Azure файла ошибок, который будет содержать ошибки, обнаруженные во время импорта. Внешний источник данных должен быть создан с помощью .TYPE = BLOB_STORAGE Дополнительные сведения см. в разделе CREATE EXTERNAL DATA SOURCE.

MAXERRORS = maximum_errors

Указывает максимальное количество синтаксических ошибок или несообразующих строк, как определено в файле форматирования, которое может возникать перед OPENROWSET созданием исключения. Пока MAXERRORS не будет достигнуто, OPENROWSET игнорирует каждую плохую строку, а не загружает ее, и подсчитывает плохую строку как одну ошибку.

Значение по умолчанию для maximum_errors — 10.

Параметры обработки данных 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) и большие типы объектов (text, ntext и image) не могут быть указаны.

SINGLE_BLOB

Возвращает содержимое файла data_file в виде набора строк с одной строкой и одним столбцом типа varbinary(max).

SINGLE_CLOB

Считывает файл data_file как ASCII-файл, возвращая содержимое в виде набора строк с одной строкой и одним столбцом типа varchar(max) и используя параметры сортировки текущей базы данных.

SINGLE_NCLOB

Считывая data_file как Юникод, возвращает содержимое в виде однострочного набора строк типа nvarchar(max), используя параметры сортировки текущей базы данных.

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

Параметры форматирования входного файла BULK

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

Указывает кодовую страницу данных в файле данных. CODEPAGEимеет значение, только если данные содержат символьные столбцы, varchar или текстовые столбцы со значениями символов более 127 или менее 32.

Значение аргумента CODEPAGE	Description
ACP	Преобразует столбцы char, varchar или текстовых данных из кодовой страницы ANSI/Microsoft Windows (ISO 1252) на кодовую страницу SQL Server.
OEM (по умолчанию)	Преобразует столбцы типа данных char, varchar или text из кодовой страницы системного изготовителя оборудования на кодовую страницу 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 = 'путь_к_файлу_форматирования'

Указывает полный путь к файлу форматирования. SQL Server поддерживает два типа файлов форматирования: XML и не XML.

Файл форматирования необходим для определения типов столбцов в результирующем наборе. Единственным исключением является, когда SINGLE_CLOBSINGLE_BLOBили SINGLE_NCLOB указан, в этом случае файл форматирования не требуется.

Сведения о файлах форматирования см. в статье "Использование файла форматирования для массового импорта данных (SQL Server)".

Начиная с SQL Server 2017 (14.x), format_file_path может находиться в Хранилище BLOB-объектов Azure. Примеры массового доступа к данным см. в Хранилище BLOB-объектов Azure.

FIELDQUOTE = "field_quote"

Начиная с SQL Server 2017 (14.x), этот аргумент задает символ, используемый в качестве символа кавычки в CSV-файле. Если не указано, символ кавычки (") используется в качестве символа кавычки, как определено в стандарте RFC 4180 .

Замечания

Функцию OPENROWSET можно использовать для доступа к удаленным данным из источников OLE DB только в том случае, если параметр реестра DisallowAdhocAccess явно содержит значение 0 для указанного поставщика, а также если включен расширенный параметр конфигурации Ad Hoc Distributed Queries. Если эти параметры не заданы, поведение по умолчанию не разрешает нерегламентированный доступ.

При доступе к удаленным источникам данных OLE DB удостоверение входа доверенных подключений не будет автоматически делегировано с сервера, на котором клиент подключен к серверу, который запрашивается. Делегирование проверки подлинности должно быть настроено.

Имена каталога или схемы необходимы, если поставщик OLE DB поддерживает несколько каталогов и схем для указанного источника данных. Значения каталога и схемы могут быть опущены, если поставщик OLE DB не поддерживает их. Если поставщик поддерживает только имена схем, необходимо указать двухкомпонентное имя в формате схема.объект. Если поставщик поддерживает только имена каталогов, необходимо указать трехкомпонентное имя в формате каталог.схема.объект. Для сквозных запросов, использующих поставщик OLE DB собственного клиента SQL Server, необходимо указать три части. Дополнительные сведения см . в соглашениях о синтаксисе Transact-SQL.

OPENROWSET не принимает переменные для своих аргументов.

Любой вызов функции OPENDATASOURCE, OPENQUERY или OPENROWSET в предложении FROM вычисляется отдельно и независимо от любого вызова этих функций, используемого как назначение при обновлении, даже если в двух таких вызовах будут заданы идентичные аргументы. В частности, условия фильтра или соединения, применяемые к результатам одного из таких вызовов, никак не влияют на результаты другого.

Использование OPENROWSET с параметром BULK

Следующие улучшения Transact-SQL поддерживают функцию OPENROWSET(BULK...) :

• Предложение FROM, используемое в инструкции SELECT, может вызывать OPENROWSET(BULK...) вместо имени таблицы с полной функциональностью инструкции SELECT.

  Функции OPENROWSET с параметром BULK требуется корреляционное имя, также известное как переменная диапазона или псевдоним в предложении FROM. Могут быть указаны псевдонимы столбцов. Если список псевдонимов столбцов не указан, файл форматирования должен иметь имена столбцов. Указание псевдонимов столбцов переопределяет имена столбцов в файле форматирования, такие как:

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

  Внимание

  Ошибка при добавлении AS <table_alias> приведет к ошибке: msg 491, Level 16, State 1, Line 20 A Необходимо указать имя корреляции для набора массовых строк в предложении 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_TRIGGERS, KEEPDEFAULTS и KEEPIDENTITY. Дополнительные сведения см. в статье Указания по таблицам (Transact-SQL).

  Сведения об использовании инструкций INSERT...SELECT * FROM OPENROWSET(BULK...) см. в статье Массовый импорт и экспорт данных (SQL Server). Сведения о том, когда операции вставки строк, выполняемые массовым импортом, регистрируются в журнале транзакций, см. в разделе "Предварительные требования для минимального ведения журнала в массовом импорте".

Массовый импорт данных SQLCHAR, SQLNCHAR или SQLBINARY

OPENROWSET(BULK...) Предполагает, что, если не указано, максимальная длина SQLCHAR, SQLNCHARили SQLBINARY данные не превышают 8000 байт. Если импортируемые данные хранятся в поле данных бизнес-объекта, содержащего любые объекты varchar(max),nvarchar(max)или varbinary(max), превышающие 8000 байт, необходимо использовать XML-файл форматирования, определяющий максимальную длину поля данных. Чтобы указать максимальную длину, измените файл форматирования и объявите атрибут MAX_LENGTH.

Массовый экспорт или импорт документов SQLXML

Чтобы выполнить массовый экспорт или импорт SQLXML-данных используйте один из следующих типов данных в файле форматирования:

Тип данных	Действие
SQLCHAR или SQLVARYCHAR	Данные отправляются на клиентской кодовой странице или на кодовой странице, подразумеваемой параметрами сортировки.
SQLNCHAR или SQLNVARCHAR	Данные отправляются в Юникоде.
SQLBINARY или SQLVARYBIN	Данные отправляются без преобразования.

Разрешения

Разрешения функции OPENROWSET определяются разрешениями имени пользователя, передаваемого поставщику OLE DB. Для использования параметра BULK требуется разрешение ADMINISTER BULK OPERATIONS или ADMINISTER DATABASE BULK OPERATIONS.

Примеры

В этом разделе приведены общие примеры использования OPENROWSET.

А. Использование OPENROWSET с select и поставщиком OLE DB собственного клиента SQL Server

Применимо только к SQL Server.

Собственный клиент SQL Server (часто сокращенный SNAC) был удален из SQL Server 2022 (16.x) и SQL Server Management Studio 19 (SSMS). Поставщик OLE DB собственного клиента SQL Server (SQLNCLI или SQLNCLI11) и устаревший поставщик Microsoft OLE DB для SQL Server (SQLOLEDB) не рекомендуется для новой разработки. Перейдите на новый драйвер Microsoft OLE DB (MSOLEDBSQL) для SQL Server .

В следующем примере поставщик OLE DB собственного клиента SQL Server используется для доступа к HumanResources.Department таблице в AdventureWorks2022 базе данных на удаленном сервере Seattle1. (Использование SQLNCLI и SQL Server будет перенаправлено на последнюю версию поставщика OLE DB собственного клиента SQL Server.) Инструкция SELECT используется для определения возвращаемого набора строк. Строка поставщика содержит ключевые слова Server и Trusted_Connection. Эти ключевые слова распознаются поставщиком OLE DB собственного клиента SQL Server.

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 для Jet

Применимо только к SQL Server.

Следующий пример обращается к Customers таблице в базе данных Microsoft Access Northwind с помощью поставщика Microsoft OLE DB для Jet.

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

C. Использование OPENROWSET и другой таблицы в INNER JOIN

Применимо только к SQL Server.

В следующем примере выбираются все данные из таблицы из Customers локального экземпляра базы данных SQL Server 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;

D. Использование 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

Е. Использование поставщика OPENROWSET BULK с файлом форматирования для извлечения строк из текстового файла

Применимо только к SQL Server.

В следующем примере используется файл форматирования для получения строк, разделенных символами табуляции, из файла 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;

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;

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'
);

I. Доступ к данным из файла, хранящегося на Хранилище BLOB-объектов Azure

Применяется только к SQL Server 2017 (14.x) и более поздним версиям.

В SQL Server 2017 (14.x) и более поздних версиях в следующем примере используется внешний источник данных, указывающий на контейнер в учетной записи хранения Azure и учетные данные базы данных, созданные для подписанного URL-адреса.

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

Полные OPENROWSET примеры, включая настройку учетных данных и внешнего источника данных, см. в примерах массового доступа к данным в Хранилище BLOB-объектов Azure.

J. Импорт в таблицу из файла, хранящегося на Хранилище BLOB-объектов Azure

В следующем примере показано, как использовать OPENROWSET команду для загрузки данных из CSV-файла в расположении хранилища BLOB-объектов Azure, в котором вы создали ключ SAS. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных. Для этого требуются учетные данные для базы с подписанным URL-адресом, зашифрованным с помощью главного ключа в пользовательской базе данных.

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

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

Затем загрузите данные из CSV-файла, размещенного в хранилище BLOB-объектов:

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

L. Использование OPENROWSET для доступа к нескольким файлам Parquet с помощью хранилища объектов, совместимого с S3

Область применения: 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 2-го поколения

Область применения: SQL Server 2022 (16.x) и более поздних версий.

В этом примере контейнер таблицы данных называется Contosoи находится в учетной записи хранения Azure Data Lake 2-го поколения.

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;

Дополнительные примеры

Дополнительные примеры использования INSERT...SELECT * FROM OPENROWSET(BULK...)см. в следующих статьях:

• Примеры массового импорта и экспорта XML-документов (SQL Server)
• Сохранение значений удостоверений при массовом импорте данных (SQL Server)
• Сохранение значений NULL или значений по умолчанию во время массового импорта (SQL Server)
• Использование файла форматирования для массового импорта данных (SQL Server)
• Использование формата символов для импорта или экспорта данных (SQL Server)
• Использование файла форматирования для пропуска столбца таблицы (SQL Server)
• Использование файла форматирования для пропуска поля данных (SQL Server)
• Использование файла форматирования для сопоставления столбцов таблиц с полями файлов данных (SQL Server)
• Запрос источников данных с помощью OPENROWSET в Управляемый экземпляр SQL Azure

Связанный контент

• DELETE (Transact-SQL)
• Предложение FROM плюс JOIN, APPLY, PIVOT (Transact-SQL)
• Массовый импорт и экспорт данных (SQL Server)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• sp_addlinkedserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)