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
инструкцией.
Внимание
База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.
Аргументы 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 может находиться в Хранилище BLOB-объектов Azure. Примеры массового доступа к данным см. в Хранилище BLOB-объектов Azure.
Внимание
База данных SQL 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.
Примечание.
MAX_ERRORS
не применяется к CHECK
ограничениям или к преобразованию денежных и больших типов данных.
Параметры обработки данных 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).
Внимание
Рекомендуется импортировать 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 = { 'ACP' | 'OEM' | 'RAW' | 'code_page' }
Указывает кодовую страницу данных в файле данных.
CODEPAGE
имеет значение, только если данные содержат символьные столбцы, varchar или текстовые столбцы со значениями символов более 127 или менее 32.
Внимание
CODEPAGE
не поддерживается в Linux.
Примечание.
Рекомендуется указывать имя параметра сортировки для каждого столбца в файле форматирования, кроме случаев, когда параметр 65001 должен иметь приоритет над спецификацией параметров сортировки или кодовой страницы.
Значение аргумента 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_CLOB
SINGLE_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). Сведения о том, когда операции вставки строк, выполняемые массовым импортом, регистрируются в журнале транзакций, см. в разделе "Предварительные требования для минимального ведения журнала в массовом импорте".
Примечание.
При использовании OPENROWSET
важно понимать, как SQL Server обрабатывает олицетворение. Дополнительные сведения о безопасности см. в статье "Использование BULK INSERT" или 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.
Примечание.
В этом примере предполагается, что Microsoft 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
Применимо только к SQL Server.
В следующем примере выбираются все данные из таблицы из Customers
локального экземпляра базы данных SQL Server Northwind
и из Orders
таблицы из базы данных Access Northwind
, хранящейся на одном компьютере.
Примечание.
В этом примере предполагается, что 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 для данных ФАЙЛА 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
Внимание
База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.
Е. Использование поставщика 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;
Внимание
База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.
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;
Внимание
База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.
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-разрядным.
Откройте вкладку "Драйверы" приложения "Подключение к источнику данных ODBC" (мастер импорта и экспорта SQL Server) в Windows, чтобы проверить это. Существует 32-разрядная версия, которая не будет работать с 64-разрядной Microsoft Text Driver (*.txt, *.csv)
версией sqlservr.exe
.
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;
Внимание
База данных SQL Azure поддерживает только чтение из хранилища BLOB-объектов Azure.
K. Использование управляемого удостоверения для внешнего источника
применимо к: Управляемому экземпляру SQL Azure и базе данных SQL Azure
В следующем примере создаются учетные данные с помощью управляемого удостоверения, также создается внешний источник, а затем данные загружаются из 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...)
см. в следующих статьях:
- Примеры массового импорта и экспорта 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)