И. Применение файла форматирования для выполнения массового импорта данных с помощью программы bcp

При выполнении импорта данных в экземпляр SQL Server используйте ключ -f с параметром in, чтобы использовать ранее созданный файл форматирования. Например, следующая команда выполняет массовое копирование содержимого файла данных Currency.dat в копию таблицы Sales.Currency (Sales.Currency2) с помощью ранее созданного файла форматирования (Currency.xml). Пример предполагает, что используется проверка подлинности Windows и существует доверительное соединение с экземпляром сервера, на котором запускается команда bcp.

В командной строке Windows введите:

bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -f Currency.xml

Примечание

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

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

Следующие разделы содержат примеры использования программы bcp:

· Копирование данных между различными параметрами сортировки

· Создание файла форматирования

· Примеры массового импорта и экспорта XML-документов

· Сохранение значений идентификаторов при массовом импорте данных

· Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных

· Определение признаков конца поля и строки

· Использование файла форматирования для массового импорта данных

· Применение символьного формата при импорте и экспорте данных

· Использование собственного формата для импорта и экспорта данных

· Использование символьного формата Юникода для импорта и экспорта данных

· Использование собственного формата Юникода для импорта или экспорта данных

OPENROWSET (Transact-SQL)

SQL Server 2008 R2

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

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

Синтаксические обозначения в Transact-SQL

Синтаксис

OPENROWSET ({ 'provider_name', { 'datasource'; 'user_id'; 'password' | 'provider_string' }, { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file', { FORMATFILE = 'format_file_path' [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }}) <bulk_options>::= [, CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [, ERRORFILE = 'file_name' ] [, FIRSTROW = first_row ] [, LASTROW = last_row ] [, MAXERRORS = maximum_errors ] [, ROWS_PER_BATCH = rows_per_batch ] [, ORDER ({ column [ ASC | DESC ] } [,...n ]) [ UNIQUE ]

Аргументы

'provider_name'

Символьная строка, представляющая понятное имя (или идентификатор PROGID) поставщика OLE DB, указанное в реестре. Аргумент provider_name не имеет значения по умолчанию.

'datasource'

Строковая константа, соответствующая указанному источнику данных OLE DB. Аргумент datasource является свойством DBPROP_INIT_DATASOURCE, передаваемым интерфейсу IDBProperties поставщика для его инициализации. Обычно эта строка содержит имя файла базы данных, имя сервера баз данных или имя, с помощью которого поставщик находит базу или базы данных.

'user_id'

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

'password'

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

'provider_string'

Строка соединения, зависящая от поставщика, которая передается как свойство DBPROP_INIT_PROVIDERSTRING для инициализации поставщика OLE DB. Аргумент provider_string обычно включает в себя все сведения о соединении, необходимые для инициализации поставщика. Список ключевых слов, распознаваемых поставщиком OLE DB для собственного клиента SQL Server, см. в разделе Свойства инициализации и авторизации.

catalog

Имя каталога или базы данных, в котором хранится указанный объект.

schema

Имя схемы или владелец указанного объекта.

object

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

'query'

Строковая константа, посылаемая поставщику и исполняемая им. Локальный экземпляр SQL Server не обрабатывает этот запрос, но обрабатывает результаты запроса, возвращаемые поставщиком, это так называемый транзитный запрос. Передаваемые запросы полезны при использовании поставщиков, которые не предоставляют свои табличные данные через таблицы имен, а только через командный язык. Передаваемые запросы поддерживаются на удаленном сервере настолько, насколько поставщик запросов поддерживает объект OLE DB Command и его обязательные интерфейсы. Дополнительные сведения см. в разделе Справочник по собственному клиенту SQL Server (OLE DB).

BULK

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

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

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

Примечание

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

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

'data_file'

Полный путь к файлу данных, данные из которого копируются в целевую таблицу.

FORMATFILE ='format_file_path'

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

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

Дополнительные сведения о файлах форматирования см. в разделе Использование файла форматирования для массового импорта данных.

< bulk_options >

Указывает один или более аргументов для параметра BULK.

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

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

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

ERRORFILE ='file_name'

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

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

FIRSTROW =first_row

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

LASTROW =last_row

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

MAXERRORS =maximum_errors

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

Значение по умолчанию для аргумента maximum_errors равно 10.

Примечание

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

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

Замечания

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

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

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

Функция OPENROWSET не принимает переменные в качестве своих аргументов.

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