OPENROWSET (Transact-SQL)

Статья
07/24/2023

Применяется к:SQL Server Azure SQL Database Azure, управляемому экземпляру SQL Azure

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

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

Примечание.

Эта статья не относится к Azure Synapse Analytics.

Дополнительные сведения об использовании OPENROWSET с бессерверными пулами SQL в Azure Synapse см. в статье Использование OPENROWSET с бессерверным пулом SQL в Azure Synapse Analytics.
Функция OPENROWSET не поддерживается в выделенных пулах SQL в Azure Synapse.

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

Синтаксис

OPENROWSET
( { 'provider_name' 
    , { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
    , {   <table_or_view> | 'query' }
   | BULK 'data_file' ,
       { FORMATFILE = 'format_file_path' [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

<table_or_view> ::= [ catalog. ] [ schema. ] object

<bulk_options> ::=

   [ , DATASOURCE = 'data_source_name' ]

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

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) [ UNIQUE ] ]
  
   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = 'CSV' ]
   [ , FIELDQUOTE = 'quote_characters']
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

Примечание.

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

'provider_name'

Символьная строка, которая представляет понятное имя (или PROGID) поставщика OLE DB, указанное в реестре. Аргумент provider_name не имеет значения по умолчанию. Примеры имен поставщиков: Microsoft.Jet.OLEDB.4.0, SQLNCLI или MSDASQL.

'datasource'

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

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

Использует поставщик больших наборов строк для функции OPENROWSET, чтобы читать данные из файла. В SQL Server OPENROWSET может считывать данные из файла данных без загрузки данных в целевую таблицу. Это позволяет использовать функцию OPENROWSET совместно с обычной инструкцией SELECT.

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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

Дополнительные сведения об использовании параметра BULK см. в подразделе «Примечания» далее в этом разделе. Дополнительные сведения о разрешениях, необходимых параметру BULK, см. в подразделе «Разрешения» далее в этом разделе.

Примечание.

Функция OPENROWSET (BULK ...) не оптимизирует ведение журнала при использовании ее для импорта данных с моделью полного восстановления.

Сведения о подготовке данных к массовому импорту см. в разделе Подготовка данных к массовому экспорту или импорту (SQL Server).

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.

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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

ERRORFILE

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

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

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

Примечание.

MAX_ERRORS не применяется к ограничениям CHECK или преобразованиям типов money и bigint.

BULK: параметры обработки данных

FIRSTROW

FIRSTROW =first_row

Указывает номер первой строки для загрузки. Значение по умолчанию — 1. Значение по умолчанию — первая строка указанного файла данных. Номера строк определяются с помощью подсчета признаков конца строки. Значения аргумента FIRSTROW начинаются с 1.

LASTROW

LASTROW =last_row

Указывает номер последней строки для загрузки. Значение по умолчанию — 0. Оно указывает на последнюю строку в используемом файле данных.

ROWS_PER_BATCH

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

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] ). Необязательное указание, определяющее способ сортировки данных в файле данных. По умолчанию массовая операция считает, что файл данных не упорядочен. Производительность можно повысить, если оптимизатор запросов сможет использовать заданный порядок для создания более эффективного плана запроса. Ниже приведены примеры, в которых указано, когда полезно назначить сортировку.

Вставка строк в таблицу с кластеризованным индексом, в которой данные набора строк сортируются по ключу кластеризованного индекса.
Соединение набора строк с другой таблицей с совпадающими столбцами сортировки и соединения.
Статистическая обработка данных набора строк по столбцам сортировки.
Использование набора строк как исходной таблицы в предложении FROM запроса с совпадающими столбцами сортировки и соединения.

UNIQUE

UNIQUE указывает, что в файле данных нет повторяющихся записей.

Если собственные строки файла данных не отсортированы в соответствии с заданным порядком или если задано указание UNIQUE и присутствуют повторяющиеся ключи, то будет возвращена ошибка.

При использовании ORDER обязательны псевдонимы столбцов. Список псевдонимов столбцов должен ссылаться на производную таблицу, к которой обращается предложение BULK. Имена столбцов, указанных в предложении ORDER, ссылаются на список псевдонимов столбцов. Нельзя указывать столбцы типов больших значений (varchar(max), nvarchar(max), varbinary(max) и xml) и типов больших объектов (text, ntext и image).

SINGLE_BLOB

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

Важно!

XML-данные рекомендуется импортировать с помощью параметра SINGLE_BLOB, а не SINGLE_CLOB или SINGLE_NCLOB, потому что только параметр SINGLE_BLOB поддерживает все возможные преобразования кодировок в Windows.

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

CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } Задает кодовую страницу для данных в файле данных. Аргумент CODEPAGE имеет смысл только в том случае, если данные содержат столбцы типа char, varchar или text с символами, коды которых больше 127 или меньше 32.

Важно!

CODEPAGE не поддерживается в Linux.

Примечание.

Рекомендуется указывать имя параметра сортировки для каждого столбца в файле форматирования, кроме случаев, когда параметр 65001 должен иметь приоритет над спецификацией параметров сортировки или кодовой страницы.

Значение аргумента CODEPAGE	Описание
ACP	Преобразует столбцы char, varchar или текстовых данных из кодовой страницы ANSI/Microsoft Windows (ISO 1252) на кодовую страницу SQL Server.
OEM (по умолчанию)	Преобразует столбцы типа данных char, varchar или text из кодовой страницы системного изготовителя оборудования на кодовую страницу SQL Server.
НЕОБРАБОТАННЫЕ	Преобразование из одной кодовой страницы в другую не выполняется. Это наиболее быстрый параметр.
code_page	Показывает исходную кодовую страницу, в которой представлены символы в файле данных, например 850. Важные версии до SQL Server 2016 (13.x) не поддерживают кодовую страницу 65001 (кодировка UTF-8).

FORMAT

FORMAT= CSV

Начиная с SQL Server 2017 (14.x), указывает файл значений, разделенный запятыми, соответствующий стандарту RFC 4180 .

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

FORMATFILE

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

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

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

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

FIELDQUOTE

FIELDQUOTE= "field_quote"

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

Замечания

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

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

Имена каталога или схемы необходимы, если поставщик OLE DB поддерживает несколько каталогов и схем для указанного источника данных. Значения аргументов catalog и schema можно не указывать, если поставщик 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. Дополнительные сведения см. в разделе Импорт данных в 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). Сведения о том, когда в журнале транзакций регистрируются операции вставки строк, выполняемые при массовом импорте, см. в разделе Предварительные условия для минимального протоколирования массового импорта данных.

Примечание.

При использовании OPENROWSETважно понимать, как SQL Server обрабатывает олицетворение. Сведения о вопросах безопасности см. в разделе Импорт данных в SQL Server при помощи инструкции BULK INSERT или OPENROWSET(BULK...) (SQL Server).

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

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

Примечание.

Автоматически сформированный файл форматирования не задает длину или максимальную длину для поля LOB. Однако можно изменить файл форматирования и указать длину или максимальную длину вручную.

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

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

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

Разрешения

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

Примеры

A. Использование функции OPENROWSET с инструкцией SELECT и поставщиком OLE DB для собственного клиента 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;

Б. Использование поставщика Microsoft OLE DB для Jet

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

Примечание.

В этом примере предполагается, что Access установлен. Для запуска данного примера необходимо установить базу данных Northwind.

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

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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

В следующем примере выбираются все данные из таблицы из 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 ;

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

D. Использование функции OPENROWSET для массовой вставки данных из файла в столбец типа varbinary(max)

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

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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

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

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

Е. Указание файла форматирования и кодовой страницы

В приведенном ниже примере показано, как одновременно использовать параметры файла форматирования и кодовой страницы.

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;

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

H. Доступ к данным из CSV-файла без использования файла форматирования

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-разрядным. Откройте вкладку Драйверы приложения Источники данных OBDC в Windows, чтобы проверить это. Есть 32-разрядный Microsoft Text Driver (*.txt, *.csv), который не будет работать с 64-разрядной версией sqlservr.exe.
База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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

Начиная с 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***************';

 -- NOTE: Make sure that you don't have a leading ? in 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= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);

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;

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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

Важно!

База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.

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...) см. в следующих статьях:

OPENROWSET (Transact-SQL)

Синтаксис

Аргументы

'provider_name'

'datasource'

'user_id'

'password'

'provider_string'

<table_or_view>

'query'

BULK

BULK 'data_file'

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

ERRORFILE

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS

BULK: параметры обработки данных

FIRSTROW

LASTROW

ROWS_PER_BATCH

ORDER

UNIQUE

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

BULK: параметры формата входного файла

CODEPAGE

FORMAT

FORMATFILE

FIELDQUOTE

Замечания

Применение инструкции OPENROWSET с параметром BULK

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

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

Разрешения

Примеры

A. Использование функции OPENROWSET с инструкцией SELECT и поставщиком OLE DB для собственного клиента SQL Server

Б. Использование поставщика Microsoft OLE DB для Jet

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

D. Использование функции OPENROWSET для массовой вставки данных из файла в столбец типа varbinary(max)

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

Е. Указание файла форматирования и кодовой страницы

G. Доступ к данным из CSV-файла с помощью файла форматирования

H. Доступ к данным из CSV-файла без использования файла форматирования

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

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

K. Использование управляемого удостоверения для внешнего источника

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

M. Использование OPENROWSET для доступа к нескольким разностным файлам из Azure Data Lake 2-го поколения

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

Дальнейшие действия

Обратная связь

Обратная связь

Дополнительные ресурсы

Дополнительные ресурсы